Forum Discussion

VoltesDev's avatar
VoltesDev
Helper V
7 months ago
Solved

Matrix subtotal

Hi, Just wondering if the current Power BI Matrix Subtotal have the ability to change Sum to Avg.   Also if I have 3 columns, then I turn on Row Subtotal, can we select to not calculate some of th...
  • GeraldGEmerick's avatar
    7 months ago

    VoltesDev Matrix and table totals calculate the given measure in the context of ALLSELECTED essentially. They do not calculate a sum or an average per se. You need to build that into your measure. Often, using HASONEVALUE to determine if you are in a row or a total row, you then can use SUMX or AVERAGEX for the total.

  • krishnakanth240's avatar
    7 months ago

    Hi VoltesDev 

     

    Use ISINSCOPE() to control subtotal logic

     

    You can return
    Normal value at row level
    BLANK() at subtotal level (or custom logic)
    Example
    Measure=
    IF(
    ISINSCOPE(Dim[RowField]),
    [Base Measure],
    BLANK()
    )

     

    or

     

    Custom Avg subtotal via DAX
    If you want Avg at subtotal, Sum at rows
    My Measure =
    IF(
    ISINSCOPE(Dim[RowField]),
    [Value],
    AVERAGEX(
    VALUES(Dim[RowField]),
    [Value]
    )
    )

    This is average of visible rows, not raw data.

     

    Please mark it as a solution with headup if this helps you. Thank You!