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 the correct condition under the "test" column but cant get to correctly total the max column. Any suggestions would be greatly appreciated. Thanks so much!

 
 
 

  

9 Replies

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

    You haven't mentioned what you expect the total to be for the max column. Max, average, something else?

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

  • stevedep's avatar
    stevedep
    Icon for Memorable Member rankMemorable Member
    6 years ago

    Hi,

    Its more simple when using an interator:

     

    _MaxOfColumns = MAXX('Table';IF('Table'[Actual]>'Table'[Calc];'Table'[Actual];'Table'[Calc]))

     

    As seen here:

    Please mark as solution if this is what you are looking for.

    Kind regards, Steve. 

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

      stevedep the ask was to show the sum of the maxima, not the max of the max.

      • stevedep's avatar
        stevedep
        Icon for Memorable Member rankMemorable Member
        6 years ago

        lbendlin Thanks for the heads up. Its a small change to make it working:

        _SumofMaxOfColumns = SUMX('Table';IF('Table'[Actual]>'Table'[Calc];'Table'[Actual];'Table'[Calc]))

        As seen here:

        Anonymous , pls let us know if this is what you are looking for.

        Kind regards, Steve. 

        p.s. file has been updated.