# A matrix with correct subtotals

> Get started with Matrix Plume: rows, columns, and values, in-place expand and collapse, and subtotals calculated by Power BI that stay correct for ratios.

- Language: English
- Web page: https://plumevisuals.com/en/tutorials/matrice-sous-totaux-carte-de-chaleur
- Published: 2026-10-04
- Visual: Matrix Plume, technical reference: https://plumevisuals.com/ai/visuals/matrix.md
- If an option name in this tutorial differs from the technical reference, the reference is right (it is generated from the visual's code).

## At a glance

- **What the visual does.** Matrix Plume replaces the native Power BI matrix. Think of an Excel PivotTable: regions in rows, with each region expanding into its cities; years in columns; and in each cell, the revenue for that city in that year. Each group has its **subtotal**, and the matrix ends with a **grand total**. Groups expand and collapse **in place** (+ / − button), without losing your position.
- **When to use it:**
  - read a number at two levels at once (region and city, category and product);
  - cross two axes (city × year, product × month);
  - show correct subtotals, including for rates and averages;
  - spot strong and weak cells with a heatmap ★.
- **When to pick something else:** for a flat list (one row per product, with no levels or cross-tab columns), **Table Plume** is simpler. For a single highlighted number, **KPI Plume**.
- **By the end, you will know how to:**
  - split your fields between **Rows**, **Columns**, and **Values**;
  - understand and set up subtotals and grand totals;
  - expand and collapse groups, and choose which level is expanded at start;
  - set up each measure separately (units, decimals, heatmap ★);
  - choose between the stepped layout and the tabular layout.
- **What you need:** Power BI Desktop, the Matrix Plume visual installed (see [the common basics](/en/tutorials/bien-demarrer-visuels-plume)), a table with at least two nested columns (region › city), and a measure.

## The data you need

**The expected shape.** A matrix has three wells, and each one expects a specific kind of field:

- **Rows**: **columns** from your table that form a **hierarchy** (see the [glossary](/en/tutorials/bien-demarrer-visuels-plume#glossary)), meaning nested levels: a `Region` contains `City` values. The first field is the highest level.
- **Columns** (optional): **columns** whose values are spread across (`Year`, `Month`).
- **Values**: **measures** (DAX calculations; see the [glossary](/en/tutorials/bien-demarrer-visuels-plume#glossary)), evaluated at each row × column intersection.

Why measures? Because Power BI recalculates the measure **for each cell, each subtotal, and the grand total**. A margin rate written as a measure stays correct on the region row; written as a calculated column, it would be summed.

**Sample dataset**: the `Sales` table, one row per city and per year (made-up data from a home furnishings store).

| Region | City | Year | Amount | Margin |
|---|---|---|---|---|
| Nouvelle-Aquitaine | Bordeaux | 2024 | 301,200 | 135,500 |
| Nouvelle-Aquitaine | Bordeaux | 2025 | 322,900 | 141,800 |
| Nouvelle-Aquitaine | Périgueux | 2024 | 118,400 | 49,700 |
| Nouvelle-Aquitaine | Périgueux | 2025 | 126,300 | 51,800 |
| Nouvelle-Aquitaine | Limoges | 2024 | 92,400 | 40,700 |
| Nouvelle-Aquitaine | Limoges | 2025 | 99,100 | 41,600 |
| Brittany | Rennes | 2024 | 214,600 | 92,300 |
| Brittany | Rennes | 2025 | 205,300 | 86,200 |
| Brittany | Brest | 2024 | 87,900 | 36,000 |
| Brittany | Brest | 2025 | 94,800 | 39,800 |
| Occitanie | Toulouse | 2024 | 256,700 | 105,200 |
| Occitanie | Toulouse | 2025 | 281,500 | 118,200 |

**The same dataset, ready to paste.** In Power BI Desktop, on the **Modeling › New table** tab, paste:

```dax
Sales =
DATATABLE (
    "Region", STRING,
    "City", STRING,
    "Year", INTEGER,
    "Amount", DOUBLE,
    "Margin", DOUBLE,
    {
        { "Nouvelle-Aquitaine", "Bordeaux", 2024, 301200, 135500 },
        { "Nouvelle-Aquitaine", "Bordeaux", 2025, 322900, 141800 },
        { "Nouvelle-Aquitaine", "Périgueux", 2024, 118400, 49700 },
        { "Nouvelle-Aquitaine", "Périgueux", 2025, 126300, 51800 },
        { "Nouvelle-Aquitaine", "Limoges", 2024, 92400, 40700 },
        { "Nouvelle-Aquitaine", "Limoges", 2025, 99100, 41600 },
        { "Brittany", "Rennes", 2024, 214600, 92300 },
        { "Brittany", "Rennes", 2025, 205300, 86200 },
        { "Brittany", "Brest", 2024, 87900, 36000 },
        { "Brittany", "Brest", 2025, 94800, 39800 },
        { "Occitanie", "Toulouse", 2024, 256700, 105200 },
        { "Occitanie", "Toulouse", 2025, 281500, 118200 }
    }
)
```

**The measures to create** (**Modeling › New measure**, once per measure):

```dax
-- Revenue: adds up the Amount column for the visible rows
Revenue = SUM ( Sales[Amount] )

-- Margin in euros
Gross Margin = SUM ( Sales[Margin] )

-- Margin rate: margin divided by revenue.
-- Recalculated by Power BI for each cell, each subtotal, and the total: always correct.
Margin Rate = DIVIDE ( [Gross Margin], [Revenue] )

-- Each city's share of its region
Share of Region =
DIVIDE (
    [Revenue],                                               -- the cell's revenue
    CALCULATE ( [Revenue], ALLSELECTED ( Sales[City] ) )     -- the revenue of all cities in the region
)
```

Give `Margin Rate` and `Share of Region` the **Percentage** format (select the measure, then **Measure tools › Format › Percentage**).

**Why the subtotals are correct: a worked example.** In 2025, the three cities in Nouvelle-Aquitaine have a margin rate of 43.9% (Bordeaux), 41.0% (Périgueux), and 42.0% (Limoges). The simple average of these three rates would be 42.3%. But the region sold €548,300 for €235,200 of margin, which is **42.9%**. Matrix Plume shows 42.9% on the "Nouvelle-Aquitaine" row, because this subtotal is **calculated by Power BI** with the measure, not summed or averaged by the visual.

**Preparation pitfalls:**

- **A ratio in a calculated column**: it gets summed in the subtotals. Write it as a measure.
- **The order of the levels**: in **Rows**, the first field is the parent (`Region`), the next one the child (`City`). Reversing the order reverses the hierarchy.
- **Dates in columns**: if you drag a Date column into **Columns**, Power BI may offer its automatic Date Hierarchy (Year, Quarter, Month, Day). Keep the levels you need or pick the column you want directly.
- **Volume**: the matrix receives 30,000 rows per batch and loads the rest automatically.

## Step by step: your first Plume matrix in 12 minutes

We start from the data and measures in ["The data you need"](#the-data-you-need).

1. Click an empty area of the page, then the **Matrix Plume** icon in the **Visualizations pane**. An empty frame appears, with a welcome page.
2. Drag `Region`, then `City`, into the **Rows** well. The matrix shows the regions, each with a + / − button, and the cities indented below.
3. Drag `Year` into the **Columns** well. The years are spread across the columns.
4. Drag `Revenue`, then `Margin Rate`, into the **Values** well. The headers stack on two levels: the year, then the name of each measure.

5. In the **Format pane**, **Expand and collapse** card: set **Levels expanded at start** to 0. Only the three regions remain, with their subtotals.
6. Click the **+** next to "Nouvelle-Aquitaine". Its three cities appear below; the region row shows a 42.9% margin rate for 2025.
7. **Subtotals** card: set **Position** to *"Total" row below the group*. The subtotal leaves the region row and appears on a "Total" row below its cities.

8. **Column formatting** card: in the list at the top of the card, choose `Revenue`, then set **Display units** to *Thousands*. Revenue is shown in thousands for every year; `Margin Rate` doesn't change.
9. In the same list, choose `Margin Rate`, then set **Decimals (-1 = field format)** to 1.
10. **Toolbar** card: turn on **Search in rows**. A search box joins the Expand all / Collapse all buttons.
11. Try it: type "brest" in the search box, then click the 2025 `Revenue` header to sort.

**Expected result:** a matrix of 3 regions × 2 years, expanded on Nouvelle-Aquitaine, with revenue in thousands, the rate with one decimal, subtotals on "Total" rows, and a search that shows Brest with its parent region.

## Troubleshooting

| What you see | Likely cause | Fix |
|---|---|---|
| No **fx** button on the Format pane colors | Power BI doesn't offer conditional formatting to table- or matrix-type visuals. | Use the free **Cell elements** card (background, font, icons, links by rules or by measure), or a measure in **Dynamic format**. |
| The matrix doesn't open at the level you set | **Save the state in the report (edit mode)** kept the groups you expanded or collapsed by hand. | Change **Levels expanded at start** or turn off saving the state. |
| No grand total row | **Grand total row** only works if **Row subtotals** is on. | Turn on **Row subtotals**. |
| A rate is summed in the subtotals | It's a calculated column. | Replace it with a `DIVIDE` measure. |
| The heatmap applies to every measure, or to the wrong one | The list at the top of **Column formatting** was set to *All columns* or to another measure. | Choose the right measure, then change the setting. |
| **Grand total column position** is missing from the pane | No field in **Columns**, or column subtotals or the grand total column are off. | Add a field to **Columns** and turn on both settings. |
| The total column on the left scrolls instead of staying frozen | Row labels and the total take up more than 75% of the visual's width. | Lower **Maximum width (% of the visual)** or make the visual wider. |
| A collapsed year becomes an empty narrow column | **Column subtotals** is off. | Turn it on. |
| The search box doesn't appear | **Search in rows** is off by default. | Turn it on in **Toolbar**. |
| No trend is drawn | No field in **Columns**. | Add a date or a month to **Columns**. |
| The "Premium preview" watermark appears | A ★ feature is used without a key. | Paste your key or switch back to a free option (see [the common basics](/en/tutorials/bien-demarrer-visuels-plume)). |

## Key takeaways

- **Rows** = nested levels (parent, then child), **Columns** = values spread across, **Values** = measures.
- Subtotals and totals are calculated by Power BI: write your ratios as measures and they will stay correct.
- Subtotal **Position**: on the group row, or on a "Total" row below the group.
- **Levels expanded at start** sets what is open; saving the state in edit mode can override it.
- **Column formatting** is set measure by measure: check which item is selected in the list at the top of the card.

## Learn more

- [Matrix Plume: 10 how-to guides (heatmap, share, tabular…)](/en/tutorials/matrix-plume-cas-pratiques): recipes for common needs.
- [Matrix Plume: every field and setting explained](/en/tutorials/matrix-plume-champs-et-reglages): each field well and each setting.
- [Getting started with Plume visuals](/en/tutorials/bien-demarrer-visuels-plume): installation, license, styles, and settings shared by all the visuals.
- The visual's page: [Matrix Plume](/en/visuals/matrix). A question? [Support](/en/support).