Forum Discussion
Subtotals in Matrix
I have 3 columns under the "Values" category of a Matrix.
I want the Debit and Credit values to show a subtotal but the Balance value should not show a subtotal.
I fixed this issue by choosing the same font color/background color which hides the subtotal in the Balance section but when its extracted to excel (Data with current layout), the amount is showing in the subtotal row.
Is there a way to fix this so there is no subtotal in the Balance section of the Matrix visual?
gmasta1129 No, Power BI does not support per-value subtotal control in a Matrix.
If subtotals are enabled, all values get subtotals. Formatting tricks only hide them visually and will always export to Excel.
What works (real solutions):
Option 1: Use a measure that returns BLANK() at subtotal level
This is the only proper fix.Balance (no subtotal) =
IF(
ISINSCOPE(YourRowField),
[Balance],
BLANK()
)Use this measure instead of the original Balance measure in the Matrix.
This hides the subtotal logically, not visually, so it will not appear in Excel export.
Option 2: Split visuals
One Matrix for Debit and Credit (with subtotals)
One Matrix for Balance (no subtotals)
Option 3: Disable subtotals entirely
Not useful here, but included for completeness.Why formatting does not work:
Formatting only affects rendering
Export uses raw values
Subtotals are still calculated
2 Replies
gmasta1129 No, Power BI does not support per-value subtotal control in a Matrix.
If subtotals are enabled, all values get subtotals. Formatting tricks only hide them visually and will always export to Excel.
What works (real solutions):
Option 1: Use a measure that returns BLANK() at subtotal level
This is the only proper fix.Balance (no subtotal) =
IF(
ISINSCOPE(YourRowField),
[Balance],
BLANK()
)Use this measure instead of the original Balance measure in the Matrix.
This hides the subtotal logically, not visually, so it will not appear in Excel export.
Option 2: Split visuals
One Matrix for Debit and Credit (with subtotals)
One Matrix for Balance (no subtotals)
Option 3: Disable subtotals entirely
Not useful here, but included for completeness.Why formatting does not work:
Formatting only affects rendering
Export uses raw values
Subtotals are still calculated
yes, but only with DAX — not with formatting.
Power BI does not let you turn subtotals on/off per value field in a Matrix. Formatting tricks only hide it visually, which is why the value still appears when exporting to Excel.
The correct approach is to change the Balance measure so it returns BLANK() at subtotal level. You can do this using ISINSCOPE() (or HASONEVALUE() depending on your rows). For example:
Balance = IF( HASONEVALUE ( 'YourRowDimension'[RowField] ), [Balance Base], BLANK() )Balance = IF( ISINSCOPE('YourRowDimension'[RowField]), [Actual Balance Measure], BLANK() )
