cancel
Showing results for
Did you mean:
Frequent Visitor

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

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.

Koen

2 ACCEPTED SOLUTIONS
Super User

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``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

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``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements