determine the row for subtotals and totals in a matrix and calculating subtotals

[Koen_Zamia]

Hello,

Another issue with subtotals.

I have been reading a lot of these posts about subtotals, but did not came across what i was looking for.

I have a matrix with rows (StateID and SubStateID) and colums (Year & Month).

The value is a calculated average based on those columns and rows.

Example of the data.

To test my DAX syntax, I first put a "1" for the value, a "2" for subtotal and a "3" for total

For some reason, i have a 1 on subtotals

I use this DAX syntax to populate the matrix.

t_Average3 =

Var MyAvgTime = divide([t_AgeInDays],[t_CountInterventions])

Var Subtotal =

SWITCH(

TRUE(),

HASONEVALUE(StateBE[StateSequence]) && HASONEVALUE(StateBE[SubStateSequence]) , 1,

HASONEVALUE(StateBE[StateSequence]) , 2,

3)

Return Subtotal

So when completing the calculation, my subtotals which must be a sum of the values, are not correct when i have more then one substate row.

This is the formula i use.

t_Average =

Var MyAvgTime = divide([t_AgeInDays],[t_CountInterventions])

Var Subtotal =

SWITCH(

TRUE(),

HASONEVALUE(StateBE[StateSequence]) && HASONEVALUE(StateBE[SubStateSequence]) , MyAvgTime,

HASONEVALUE(StateBE[StateSequence]) , SUMX( VALUES(StateBE[SubStateSequence]) , MyAvgTime ),

SUMX( VALUES(StateBE[SubStateSequence]) , MyAvgTime ))

Return Subtotal

Who can help me with this. Any guidance is very welcome.

Thanks in advance.

Koen

[MFelix · Accepted solution]

Hi @Koen_Zamia ,

This is related with the fact that you are using HASONEVALUE, if you look at your matrix the lines where you have 1 on the subtotals you only have 1's on the lines where the substate ID is only 1 row try to change the measure to use ISINSCOPE instead of HASONEVALUE something similar to this:

t_Average =
Var MyAvgTime = divide([t_AgeInDays],[t_CountInterventions])
Var Subtotal =

SWITCH(
TRUE(),
ISINSCOPE(StateBE[StateSequence]) && ISINSCOPE(StateBE[SubStateSequence]) , MyAvgTime,
ISINSCOPE(StateBE[StateSequence]) , SUMX( VALUES(StateBE[SubStateSequence]) , MyAvgTime ),
SUMX( VALUES(StateBE[SubStateSequence]) , MyAvgTime ))
Return Subtotal

[v-yalanwu-msft · Accepted solution]

Hi, @Koen_Zamia ;

You could use ISINSCOPE() not HASONEVALUE(), such as:

t_Average3 = SWITCH(TRUE(),ISINSCOPE(StateBE[SubStateld]),1,ISINSCOPE(StateBE[Stateld]),2,3)

The final output is shown below:

If in your case , you could try it:

t_Average =
VAR MyAvgTime =
    DIVIDE ( [t_AgeInDays], [t_CountInterventions] )
VAR Subtotal =
    SWITCH (
        TRUE (),
        ISINSCOPE ( StateBE[SubStateld] ), MyAvgTime,
        ISINSCOPE ( StateBE[Stateld] ), SUMX ( VALUES ( StateBE[SubStateSequence] ), MyAvgTime ),
        SUMX ( VALUES ( StateBE[SubStateSequence] ), MyAvgTime )
    )
RETURN
    Subtotal

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix · Accepted solution]

Hi @Koen_Zamia ,

This is related with the fact that you are using HASONEVALUE, if you look at your matrix the lines where you have 1 on the subtotals you only have 1's on the lines where the substate ID is only 1 row try to change the measure to use ISINSCOPE instead of HASONEVALUE something similar to this:

t_Average =
Var MyAvgTime = divide([t_AgeInDays],[t_CountInterventions])
Var Subtotal =

SWITCH(
TRUE(),
ISINSCOPE(StateBE[StateSequence]) && ISINSCOPE(StateBE[SubStateSequence]) , MyAvgTime,
ISINSCOPE(StateBE[StateSequence]) , SUMX( VALUES(StateBE[SubStateSequence]) , MyAvgTime ),
SUMX( VALUES(StateBE[SubStateSequence]) , MyAvgTime ))
Return Subtotal