Forum Discussion
get grand total when using switch
I am trying to understand how can I display a grand total, I have a measure that does:
SWITCH(
TRUE()
,ISINSCOPE(xyz ,a+a)
,ISINSCOPE(xyz2 ,b+b)
,BLANK()
)
The grand total when I use a table appears blank... Even though I have values...
How can I make the measure display a grand total? (the grand total should be an average not a sum...)
7 Replies
Try:
SWITCH(
TRUE()
ISINSCOPE(xyz), a+a,
ISINSCOPE(xyz2), b+b,
[your average measure]
)Anonymous , You have to force a total from rows based on group bys(unsummarized column/row/axis of visual)
sumx(Values(Table[ID],
SWITCH(
TRUE()
,ISINSCOPE(xyz ,a+a)
,ISINSCOPE(xyz2 ,b+b)
,BLANK()
))also refer
Thanks amitchandak , but not sure which table should use in Table[ID]
Because I have both xyz and xyz2
Create a measure to calculate the average you want for the total. Let's call this [average measure]
The write the switch measure (assuming you wish to establish values based on the field filter context):
Switch measure =
SWITCH(TRUE(),ISINSCOPE(Table [column a]), [result for a],
ISINSCOPE(Table [column b]), [result for b],[Average measure])
