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

 

 

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
    )

     

2 Replies

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

     

  • nath2544's avatar
    nath2544
    Frequent Visitor
    1 year ago

    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.