Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Totals Question Pls

Hi all, I have this table. Actual, Calc and Max are measures. Max is the value that is greater between Actual and Calc Max rows are correct but the total is not. I was able to use "Isfilter" to get...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello, oh sorry. Sum is what I need

 

Thanks

 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

This was a nice challenge.  Here's my approach - there are probably better ones.

 

Max = if(
    HASONEVALUE('Table (3)'[Prod])
    ,max(selectedvalue('Table (3)'[Actual]),SELECTEDVALUE('Table (3)'[Calc]))
    ,sumx(Filter('Table (3)','Table (3)'[Actual]>'Table (3)'[Calc]),'Table (3)'[Actual])
    +sumx(Filter('Table (3)','Table (3)'[Actual]<='Table (3)'[Calc]),'Table (3)'[Calc])
    )

 

So if you look at the rows, just use the regular max() logic, but for the totals distinguish between the cases where the actual is bigger than the calc, and where the actual is smaller or equal.  The sum of these two scenarios gives the desired result.

 

Note: Additional filters may need to be applied depending on your data model. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks very much for the help.

     

    After modifying your equation a little I was able to make it work

     

    Here is the final eq

     

    if(ISFILTERED(Table1[Prod]),sumx(Table1,[max]),CALCULATE(SUMX(Table1,[max]),ALL(Table1[Prod])))
     
    More importantly, I understand the concept better now. Thanks very much!