Forum Discussion
Need different DAX for matrix subtotal
Hi community,
In matrix visual, for a particular measure subtotal I want to have a different calculation rather than the standard sum done by the matrix. For example in the following snapshot, 'No Show Rate' subtotal should be: total 'No show'/total that is 33/172=19% instead of standard sum of 'no show rate'.
TIA,
Meena
Hi Anonymous
Create a measure
Measure = IF ( ISINSCOPE ( 'Table 2'[column cate] ), FORMAT ( SUM ( 'Table 2'[value] ), "General Number" ), FORMAT ( ROUND ( DIVIDE ( CALCULATE ( SUM ( 'Table 2'[value] ), FILTER ( 'Table 2', 'Table 2'[sessions] = "no show" ) ), SUM ( 'Table 2'[value] ) ), 2 ), "percent" ) )Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
Anonymous ,
One way to chnage is , give the column values/the row group where you want to sum or average
Sumx(values(Table[Row Column]),[% meaure])
or
Average(values(Table[Row Column]),[% meaure])
or
Sumx(summarize(Table,Table[Row Column],Table[Row Column1],"_1" ,[% meaure]),[_1]) // more than one column
Or refer
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
v-juanli-msftCommunity Support
Hi Anonymous
Create a measure
Measure = IF ( ISINSCOPE ( 'Table 2'[column cate] ), FORMAT ( SUM ( 'Table 2'[value] ), "General Number" ), FORMAT ( ROUND ( DIVIDE ( CALCULATE ( SUM ( 'Table 2'[value] ), FILTER ( 'Table 2', 'Table 2'[sessions] = "no show" ) ), SUM ( 'Table 2'[value] ) ), 2 ), "percent" ) )Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.v-juanli-msftthanks, would you please provide the PBI file of the example you used?
- v-juanli-msft
