Row Subtotal for Matrix

[Mike_Sims]

Hi.  I am trying to average each row of a matrix across the columns, in my case years, and was hoping to do so via a subtotal.  Each row is a quick measure.  The first row is an average by each year.  The second row is a sum by each year.  The third row is a division of the second row over the first row... pretty basic stuff.  For each of these rows i need the mean average to show as a subtotal (instead of the sum subtotal).  Currently the First row is giving the average subtotal (assuming this is because it is an average measure) however the bottom two rows are adding.  I can't see easily how to fix this.  Any help would be appreciated.

Need to change the subtotal to an average

[amitchandak · Accepted solution]

@Mike_Sims , Not sure I got it. You can try measures like

Sum of avg headcount = Sumx(Values(Table[Year]), [Average headcount])

Avg of leavers = AverageX(Values(Table[Year]), [leavers])

or use isinscope to change measure

if(isinscope(Table[Year]), [Measure1], [Measure2])

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Anonymous · Accepted solution]

Hi @Mike_Sims,

Please take a look at the following blog to use Dax expression with if statement to check hierarchy levels to handle the total level calculations.

Clever Hierarchy Handling in DAX - SQLBI

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Mike_Sims , Not sure I got it. You can try measures like

Sum of avg headcount = Sumx(Values(Table[Year]), [Average headcount])

Avg of leavers = AverageX(Values(Table[Year]), [leavers])

or use isinscope to change measure

if(isinscope(Table[Year]), [Measure1], [Measure2])

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/