Totals Question Pls

[Anonymous]

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!

  

[stevedep]

@Anonymous , does this work for you?

[stevedep]

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]

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

[stevedep]

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

[lbendlin]

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

[Anonymous]

Hello, oh sorry. Sum is what I need

Thanks

[lbendlin]

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]

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!

[lbendlin]

Then you will probably like this old article

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/