Forum Discussion
Customize subtotal in matrix
I need help creating custom subtotals in a matrix visual. The client requires subtotals that aggregate only a specific subset of rows, followed by a final grand total that includes all values excluding those subtotals.
In this setup, the rows and values come from the same table, while the columns come from a different table, as illustrated in the diagram below.
| 2025 | 2026 | ||||
| Category | Sub Catgory | Jan | Feb | Jan | Feb |
| CC | CCF | 6551 | 6598 | 985 | 89 |
| WC | CRG | 516 | 3232 | 516 | 98 |
| DG | 16 | 32 | 653 | 98 | |
| GG | 56 | 32 | 3 | 98 | |
| SDF | GT | 56 | 32 | 65 | 98 |
| RT | 56 | 98 | 5 | 8 | |
| DFB | 56 | 56 | 65 | 5 | |
| SF | ER | 65 | 1 | 98 | 56 |
| RT | 65 | 65 | 65 | 21 | |
| SUBTOTAL | 7437 | 10146 | 2455 | 571 | |
| AFGH | DFG | 984 | 984 | 984 | 54 |
| E | HN | 56 | 35 | 56 | 56 |
| TOTAL | 8477 | 11165 | 3495 | 681 | |
Try this. If this sitll doens't work, there are paid custom visuals that allow insertion of custom rows.
8 Replies
Hi Jessica_17,
Below is a summary of what I implemented in the attached PBIX to support custom/dynamic SUBTOTAL rows in a Matrix without double-counting.
1) Tables Created
1. Fact: Contains the sample data (Category, SubCategory, Year/Month/Date, Amount) used for the Matrix values.
This is the table being aggregated by the measures.
2. Calendar
3. MatrixRows (Disconnected)
- A display driver table that defines which rows appear in the Matrix:
- Detail rows (Category/SubCategory)
- A custom SUBTOTAL row
- A custom TOTAL row
- It is intentionally not related to Fact so the row behavior is fully controlled via DAX (instead of built-in subtotal logic).
4. CategorySubtotalMap
A small mapping table that controls which Categories should be included in the custom SUBTOTAL.
Uses a flag column like IncludeInSubtotal (TRUE/FALSE) so the subtotal selection is dynamic and maintainable (no hard-coded lists in DAX).
2) Relationships
- Calendar[Date] (1) → Fact[FactDate] (*)
- CategorySubtotalMap[Category] (1) → Fact[Category] (*)
- No relationship for MatrixRows: MatrixRows remains disconnected by design; filtering to Fact is handled inside the measure using TREATAS.
3) Measure Used (Custom Amount)
Custom Amount returns different logic depending on the row type:
- DETAIL: returns the value for the specific Category/SubCategory (via TREATAS)
- SUBTOTAL: returns the sum for only categories where IncludeInSubtotal = TRUE
- TOTAL: returns the overall total from Fact (no double counting, since SUBTOTAL/TOTAL rows are not part of Fact)
Microsoft Learn reference (TREATAS / virtual relationships): https://learn.microsoft.com/dax/treatas-function-dax
4) Matrix Formatting
Since SUBTOTAL and TOTAL are created as explicit rows, turn built-in Matrix Subtotals and Grand Total OFF to avoid duplicate totals
If this response was helpful, please accept it as a solution and give kudos to support other community members!
HI ArwaAldoud ,
Thanks for the detailed solution. Would it be possible to arrange the subtotals in the order mentioned in my query? Specifically, the client would like each subtotal to appear directly below its corresponding category, with the remaining categories listed after.
Hi Jessica_17
You will need a disconnected table to be used as a display table and then create a measure referencing this table that conditionally return values. You can use DAX but if the categories aren't fixed you can just use enter data. Your goal is to position Subtotal right before the last two categories
Subtotal01 = VAR _category = SELECTEDVALUE ( DisplayTable[Category] ) VAR _subtotal = CALCULATE ( [Value1], KEEPFILTERS ( NOT 'Table'[Category] IN { "AFGH", "E" } ) ) VAR _other = CALCULATE ( [Value1], TREATAS ( VALUES ( DisplayTable[Category] ), 'Table'[Category] ) ) RETURN SWITCH ( TRUE (), ISINSCOPE ( 'Table'[Sub Catgory] ) && _category = "Subtotal", BLANK (), _category = "Subtotal", _subtotal, _other ) =========================================== Subtotal02 = VAR _category = SELECTEDVALUE ( DisplayTable[Category] ) VAR _subtotal = CALCULATE ( [Value1], KEEPFILTERS ( NOT 'Table'[Category] IN { "AFGH", "E" } ) ) VAR _other = CALCULATE ( [Value1], TREATAS ( VALUES ( DisplayTable[Category] ), 'Table'[Category] ) ) RETURN SWITCH ( TRUE (), _category = "Subtotal", _subtotal, _other )Please see the attached pbix.
HI danextian ,
Thanks for the response.
When I tried the same approach with my dataset, the subtotal is displaying all the subcategory names alongside it. Because of this, when I drill down, I have to manually drill back up to view only the subtotal.
Is there an alternative solution to handle this, or would you need any additional information from my side to investigate further?
Try this. If this sitll doens't work, there are paid custom visuals that allow insertion of custom rows.
I can help you
