Forum Discussion
Total Calculation for Matrix Visual
Hello Friends, can you help me on this calculation, I have a matrix visual with 2 columns say Item having values A,B,C on the Row header and year having values 2023,2024 and 2025 as column header and I have a measure which captures some numeric value on the Values. see the screenshot . I want that measure to be update such a way that it should the values of A for these 3 years and in the total part these values not be considered (Total = Sum of values - (sum of values for row A))
Hi RENJITH_R_S
You can use isinscope function to manipulate the measure accorsing to hierarchie level .Sum_ =if (ISINSCOPE('Table'[Category]),sum('Table'[Value]),CALCULATE(sum('Table'[Value]),'Table'[Category]<>"A"))Result :
The pbix with the example is attached
More information aboit isinscope here :
https://www.youtube.com/watch?v=DtOfcsS_pQw
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
5 Replies
This DAX should work, however there's probably a better way you can calculate this:
Measure = SWITCH( TRUE(), HASONEVALUE( {{YOUR_TABLE}}[ROW_COLUMN] ), CALCULATE( SUM( {{YOUR_TABLE}}[VALUE_COLUMN/MEASURE] ) ), CALCULATE( SUM( {{YOUR_TABLE}}[VALUE_COLUMN/MEASURE] ), FILTER( {{YOUR_TABLE}}, {{YOUR_TABLE}}[ROW_COLUMN] <> "A" ) ) )Basically, calculate the measure normally unless there are more than one value present (the total row). If there are more values present, ignore the selected row of data (A).
Thanks Alex_Sawdo
Hi RENJITH_R_S
You can use isinscope function to manipulate the measure accorsing to hierarchie level .Sum_ =if (ISINSCOPE('Table'[Category]),sum('Table'[Value]),CALCULATE(sum('Table'[Value]),'Table'[Category]<>"A"))Result :
The pbix with the example is attached
More information aboit isinscope here :
https://www.youtube.com/watch?v=DtOfcsS_pQw
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Ritaf1983 Thank you
Glad to help 🙂
