Customize subtotal in matrix

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!