Power BI Table Totals and Subtotals: Complete Control Guide (2026)
Add totals to a Power BI Matrix via Format pane → Row subtotals (toggle on). Control which hierarchy levels show subtotals using "Per row level" toggle. Position subtotals Top or Bottom. For broken totals with DAX measures, the fix is HASONEVALUE() or ISINSCOPE() to detect the total context and return the correct calculation. Grand total can be hidden while keeping subtotals by toggling the grand total off independently.
📋 In this guide
1. Enabling Totals and Subtotals
Table visual — grand total only
The Table visual supports only a single grand total row — no subtotals (there is no hierarchy in a flat table to create subtotals for).
- Select the Table visual
- Format pane →
Totals→ toggle on - The total row appears at the bottom by default
- Each column shows the sum of its values — or the aggregation defined in the field (sum, average, count, etc.)
Matrix visual — subtotals and grand total
The Matrix visual is where most totals configuration happens — it supports subtotals at each hierarchy level plus a grand total.
- Select the Matrix visual
- Format pane →
Row subtotals→ toggle on - Format pane →
Column subtotals→ toggle on (if you have column groupings) - Set
Row subtotal positionto Top or Bottom - To enable the overall grand total: Format pane →
Row subtotals→Grand total→ toggle on separately
Common confusion: In Power BI Matrix, "subtotals" and "grand total" are controlled separately. Toggling on Row subtotals does not automatically add a grand total — you need to toggle Grand total on independently. And hiding grand total does not remove subtotals.
2. Controlling Subtotals Per Hierarchy Level
This is the most requested totals feature and the one most people miss — the ability to show subtotals at some levels but not others.
A typical Finance P&L hierarchy has 3 levels: Group → Account → Sub-account. You want subtotals at the Group level but not at the Account or Sub-account level. Here is how:
- Format pane →
Row subtotals→ toggle on - Toggle
Per row levelto On — this reveals individual level controls - For each hierarchy level, toggle subtotals on or off independently
- Example: Level 1 (Group) = On, Level 2 (Account) = Off, Level 3 (Sub-account) = Off
If "Per row level" toggle doesn't appear: Make sure you have at least 2 fields in the Rows well of the Matrix — the per-level toggle only appears when there is a hierarchy to control.
Common per-level configurations for Finance
P&L report (Group → Account)
- Level 1 (Revenue / COGS / OpEx groups): Subtotal ON
- Level 2 (Individual GL accounts): Subtotal OFF
- Grand total: OFF (Revenue + Expenses = meaningless)
Sales by Region → Country → Product
- Level 1 (Region): Subtotal ON
- Level 2 (Country): Subtotal ON
- Level 3 (Product): Subtotal OFF
- Grand total: ON (total sales is meaningful)
Budget vs Actuals by Department
- Level 1 (Division): Subtotal ON
- Level 2 (Department): Subtotal OFF
- Grand total: ON
Headcount by Location → Team
- Level 1 (Location): Subtotal ON
- Level 2 (Team): Subtotal OFF
- Grand total: ON (total headcount is meaningful)
3. Subtotal Position — Top vs Bottom
Format pane → Row subtotals → Row subtotal position: Top or Bottom.
Bottom (default): Subtotal row appears after all rows in the group. Standard for most table layouts — data rows first, then the total. Used in most Finance reports.
Top: Subtotal row appears before the group's detail rows. A good practice is to keep subtotals at the top of groups for easy visibility when scrolling through large datasets. Useful for long groups where the user needs to see the total before drilling into details — common in operations or headcount reports with many rows per group.
For leading totals (total before the months in a column header): This requires a more complex setup using a Calculation Group and a Calendar Totals helper table. You need a transformed calendar dimension with a Totals Position column, a one-to-many bi-directional relationship between the calendar and the helper table, and a calculation group to handle the logic. This is a developer task — not a point-and-click setting.
4. Formatting Total Rows
| What to format | Format pane location | Recommended for Finance |
|---|---|---|
| Row subtotals | ||
| Background color | Row subtotals → Colors → Background | #f0f7ff (light blue) to visually separate from data rows |
| Font color | Row subtotals → Colors → Font | Dark navy (#0f172a) for emphasis |
| Font weight | Row subtotals → Font → Bold | Bold on — subtotal rows should stand out |
| Font size | Row subtotals → Font → Size | Same as data rows or 1pt larger — avoid going too large |
| Grand total | ||
| Background color | Grand total → Colors → Background | #0078d4 with white font for maximum emphasis |
| Conditional formatting on totals | Conditional formatting dialog → Apply settings to: Subtotals | Apply same red/green variance coloring to total rows as data rows |
| Column subtotals | ||
| Column subtotal background | Column subtotals → Colors | Light grey (#f3f4f6) to distinguish from data columns |
Conditional formatting on total rows: In the conditional formatting dialog (fx button), there is an "Apply settings to" dropdown. Select "Subtotals" to apply the same color rule to subtotal rows separately from data rows. This lets you have red/green variance coloring on both data and total rows without interference.
5. Fixing Broken Totals with DAX Measures
This is the most searched topic related to Power BI totals — and the most frustrating. Custom DAX measures that work perfectly on data rows show wrong numbers (or blank) in the total row.
Incorrect matrix totals almost always occur because custom measures depend on row-level filter context. At the total row, the filter context changes — row filters are removed — and measures that worked correctly for detail rows calculate incorrectly.
Why it happens
In a Matrix with Category → Product hierarchy, each data row has both Category and Product in its filter context. The total row for a Category has only Category in context — Product is removed. Grand total has neither. A measure using SELECTEDVALUE([Product]) returns blank at the total level because multiple products are selected.
Fix 1 — HASONEVALUE() for simple cases
-- Returns value for data rows, correct aggregate for totalsSafe Margin % =
IF(
HASONEVALUE('Product'[ProductKey]),
-- Detail row: use the specific product calculationDIVIDE([Gross Profit], [Revenue]),
-- Total row: aggregate correctly across all productsDIVIDE(
CALCULATE(SUM('Sales'[GrossProfit])),
CALCULATE(SUM('Sales'[Revenue]))
)
)Fix 2 — ISINSCOPE() for multi-level hierarchies
-- ISINSCOPE() detects which level is in scope-- Works correctly across all hierarchy levelsRevenue by Level =
SWITCH(
TRUE(),
-- Most specific level first (always)ISINSCOPE('Product'[ProductName]), [Product Revenue],
ISINSCOPE('Category'[CategoryName]), [Category Revenue],
-- Grand total fallback
[Total Revenue]
)Fix 3 — SUMX for ratio measures at total level
-- Common problem: % margin sums incorrectly at total row-- Wrong: AVERAGE of margins ≠ true total marginMargin % WRONG = AVERAGE('Sales'[MarginPct])
-- Correct: recalculate from totals at every levelMargin % CORRECT =
DIVIDE(
SUMX(
VALUES('Product'[ProductKey]),
[Gross Profit]
),
SUMX(
VALUES('Product'[ProductKey]),
[Revenue]
)
)Rule of thumb:Write SWITCH(TRUE()) conditions from the most specific to the most general. If you check the grand total context before the detail row context, the grand total condition will match first and the detail rows will show the wrong calculation. Most specific (lowest level of the hierarchy) always comes first.
6. Finance-Specific Patterns
P&L report — hiding grand total
A P&L that adds Revenue + COGS + Operating Expenses in a grand total produces a meaningless number. The right setup:
- Format pane → Row subtotals → Per row level → ON
- Level 1 (P&L group: Revenue, COGS, OpEx): Subtotal ON — shows group total
- Level 2 (GL accounts): Subtotal OFF
- Grand total → toggle OFF — the P&L grand total is meaningless
- Format subtotal rows: bold font, light blue background to separate from account rows
Budget vs Actuals — showing variance at total level
Variance % measure shows correct values per department, but at the division total row it shows a simple average of department variances instead of the true total variance.
Use DIVIDE([Total Actual] - [Total Budget], ABS([Total Budget])) at the total level rather than averaging the department-level percentages. Wrap with HASONEVALUE([Department]) to detect data rows vs total rows and return the appropriate calculation for each.
Hiding subtotals for specific measures only
The Format pane controls subtotals for all measures at once — you cannot show subtotals for Revenue but hide them for headcount in the same Matrix without a DAX workaround.
The workaround: create a version of the measure that returns BLANK() when at the total level for the measures you want to suppress:
-- Returns blank at total level (suppresses total for this measure)Headcount (no total) =
IF(
HASONEVALUE('Department'[DepartmentKey]),
[Headcount],
BLANK()
)7. Limits — What Still Requires a Workaround
Even with full access to the Format pane and DAX, there are specific totals behaviors that native Power BI cannot handle cleanly:
- Custom subtotals that use different logic from data rows — for example, showing Revenue Share % in the column subtotal but Revenue amount in data columns. This requires a Calculation Group — a developer task.
- Row-specific subtotal labels — renaming "Total" to "Total Revenue" or "Net Income" per subtotal row is not supported natively. Workarounds exist using virtual tables but are complex.
- End users controlling subtotals after publishing — once a report is published, Finance users cannot toggle subtotals on or off, change position, or add subtotals for a different level. Every subtotals configuration change requires the developer to republish.
- Subtotals for a subset of rows — for example, subtotaling only the "Operating" accounts but not the "Non-operating" accounts within the same Matrix. This requires DAX measures that return BLANK() strategically.
For Finance teams that need ongoing control over subtotals — changing which rows are summarized, adding subtotals for different groupings without developer involvement — custom table visuals like Flexa Tables provide self-service subtotal control directly in the published report.
Self-Service Subtotals and Table Restructuring in Published Reports
Flexa Tables gives Finance teams direct control over table layout, row grouping, and subtotals in the published Power BI report — without going back to Desktop or writing DAX.
- Add and remove subtotal rows for custom row groupings
- Drag and drop to restructure rows and columns after publishing
- Add MoM, YoY, DoD variance columns with one click — no DAX
- Finance controls the layout — developer not involved
- $2.99/user/month — Microsoft-certified, free trial on AppSource
Frequently Asked Questions
How do I add totals and subtotals to a Power BI matrix?
Format pane → Row subtotals → toggle on. For per-level control, set "Per row level" to On and configure each hierarchy level individually. Grand total is toggled separately from subtotals.
Why are my Power BI matrix totals showing wrong numbers?
Custom DAX measures often break at the total level because filter context changes — row-level filters are removed. Fix using HASONEVALUE() to detect the total context, or ISINSCOPE() for multi-level hierarchies. For ratio measures (% margin), use SUMX to recalculate from totals rather than averaging row values.
How do I show subtotals at specific levels only in a Power BI matrix?
Format pane → Row subtotals → Per row level → On. Individual level toggles appear — turn on/off independently for each hierarchy level. Requires at least 2 fields in the Matrix Rows well.
Can I hide the grand total but keep subtotals in Power BI?
Yes — Row subtotals controls subtotals and grand total independently. Toggle grand total off while keeping row subtotals on. Common in P&L reports where the grand total is not meaningful.
How do I format the total row differently from data rows in Power BI?
Format pane → Row subtotals → Colors to set different background and font color. For conditional formatting on total rows, use the "Apply settings to: Subtotals" option in the conditional formatting dialog.
What is the difference between totals and subtotals in Power BI?
Subtotals are aggregate rows at each hierarchy level within the Matrix. Grand total is the single overall total for the entire visual. Both are controlled independently in Format pane → Row subtotals.
