Total Calculation for Matrix Visual

[RENJITH_R_S]

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

[Ritaf1983 · Accepted solution]

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

[RENJITH_R_S]

@Ritaf1983 Thank you

[Ritaf1983]

Glad to help 🙂

[Alex_Sawdo]

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). 

[RENJITH_R_S]

Thanks @Alex_Sawdo