Matrix Visual custom calculation

[nath2544]

Hi,
from this picture is my sample matrix visual. I want to make a custom calculation for each Category. When put value in matrix table it will sum value of each item automatically. But what if I want a different calculation. Example, the total value of Category 2 is sum of all item in Category 2 + total of Category 3. And the total value of Category 2 must be the total value of Category 1 subtracted by the total value of Category 2.

Is there any solution or workaround? Any suggestion is appreciated.

Expected result,

[nath2544]

This solution works. But what if the same sub_category belongs to another category like sub_cat A it refers to category 2.
As far as I know, the relationship will be broken because the sub_category is a primary key here.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

ISINSCOPE function (DAX) - DAX | Microsoft Learn

expected result measure: = 
VAR _valuetotal = [value total:]
VAR _subcategory2result =
    CALCULATE ( SUM ( data[Value] ), sub_category[category] = 2 )
        + CALCULATE ( SUM ( data[Value] ), sub_category[category] = 3 )
VAR _subcategory3result =
    CALCULATE ( SUM ( data[Value] ), sub_category[category] = 1 )
        - CALCULATE ( SUM ( data[Value] ), sub_category[category] = 2 )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( sub_category[sub_category] ), _valuetotal,
        ISINSCOPE ( sub_category[category] )
            && MAX ( sub_category[category] ) = 2, _subcategory2result,
        ISINSCOPE ( sub_category[category] )
            && MAX ( sub_category[category] ) = 3, _subcategory3result
    )