Using IF statement in Measure on group sum

[GWahlers]

I am using a measure with an if-clause on a matrix visual. The values within the subdimension are correct but not on the parent dimension. The if -statement even affects the row sum, instead of summing all child values up. What am i doing wrong?

[GWahlers]

Hi Liu Yang,
but if i try changing the "Group" to "Zip Code" or "Country" the values will be wrong. Is it a bug, or why does power bi apply the if condition even on the summarizing rows? 

Kind Regards, 
Gerrit Wahlers

[Anonymous]

Hi  @GWahlers ,

I created some data:

In the matrix, you can use IF+ ISINSCOPE() to determine the different levels.

ISINSCOPE function (DAX) - DAX | Microsoft Learn

Here are the steps you can follow：

1. Create measure.

Flag =
IF(      
        ISINSCOPE('Table'[Group2]),
        SUMX(
            FILTER(ALL('Table'),
            'Table'[Group1]=MAX('Table'[Group1])&&'Table'[Group2]=MAX('Table'[Group2])),[Measure])
        ,
IF(
    ISINSCOPE('Table'[Group1]),
    SUMX(
          FILTER(ALL('Table'),
            'Table'[Group1]=MAX('Table'[Group1])),[Measure])
    ,       
        SUMX(ALL('Table'),[Measure])      
        ))

2. Result:

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly