Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Var Subtotal =
Return Subtotal
Who can help me with this. Any guidance is very welcome.
Thanks in advance.
Koen
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi, @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.
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.
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êsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |