topic Totals Question Pls in DAX Commands and Tips

Totals Question Pls

Anonymous — Sun, 14 Jun 2020 20:30:17 GMT

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!

Re: Totals Question Pls

lbendlin — Sun, 14 Jun 2020 20:32:53 GMT

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

Re: Totals Question Pls

Anonymous — Sun, 14 Jun 2020 20:34:06 GMT

Hello, oh sorry. Sum is what I need

Thanks

Re: Totals Question Pls

lbendlin — Sun, 14 Jun 2020 23:43:36 GMT

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.

Re: Totals Question Pls

Anonymous — Mon, 15 Jun 2020 01:22:19 GMT

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!

Re: Totals Question Pls

lbendlin — Mon, 15 Jun 2020 02:23:31 GMT

Then you will probably like this old article

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

Re: Totals Question Pls

stevedep — Mon, 15 Jun 2020 06:11:05 GMT

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.

Re: Totals Question Pls

lbendlin — Mon, 15 Jun 2020 12:28:07 GMT

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

Re: Totals Question Pls

stevedep — Wed, 17 Jun 2020 10:39:05 GMT

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.

Re: Totals Question Pls

stevedep — Thu, 18 Jun 2020 15:26:07 GMT

Anonymous , does this work for you?