Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
8 months ago
Solved

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 choosin...
Need Help
  • Amar_Kumar's avatar
    Amar_Kumar
    8 months ago

    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

cengizhanarslan's avatar
cengizhanarslan
Super User
8 months ago

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()
)