Forum Discussion

nath2544's avatar
nath2544
Frequent Visitor
1 year ago
Solved

Matrix Visual custom calculation

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 wa...
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
1 year ago

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
    )