PowerPivot: How to calculate the MAX of three measures?

[mikebutak]

Hello Community,

I'm a newbie to powerpivot/dax.

I have created three measures: 2011 sales, 2012 sales, and 2013 sales (a simple calculation, sum sales where year = 2011, etc...)

I wish to create a 4th measure: 3-Yr Max Sales, which calculates the max of 2011 Sales, 2012 Sales, and 2013 Sales. I don't see how MAX, MAXA or MAXX can be used to accomplish this. I also have tried using a long if statement, "If 2011 is greater than 2012 and 2013, etc."

Any ideas??

Thank you,
Mike

 

[mikebutak]

Hello Community,

I'm a newbie to powerpivot/dax.

I have created three measures: 2011 sales, 2012 sales, and 2013 sales (a simple calculation, sum sales where year = 2011, etc...)

I wish to create a 4th measure: 3-Yr Max Sales, which calculates the max of 2011 Sales, 2012 Sales, and 2013 Sales. I don't see how MAX, MAXA or MAXX can be used to accomplish this. I also have tried using a long if statement, "If 2011 is greater than 2012 and 2013, etc."

Any ideas??

Thank you,
Mike

I have discovered a very inelegant solution:

Just to review, I have 3 measures:
2011 Sales
2012 Sales
2013 Sales

I wish to create a 3-Yr Max.

I do so by a two-step process:

1. Create a fourth measure, Max of 2011 and 2012 Sales:=if([2011 Sales]>[2012 Sales],[2011 Sales],[2012 Sales])
2. Create a fifth measure, 3-Yr max Sales:=if([2013 Sales]>[Max of 2011 and 2012 Sales],[2013 Sales],[Max of 2011 and 2012 Sales])

Pretty dorky, but it gets me where I need to go.





Next question:
How to rank the results of that measure? (I could use rank in pivot table, but I'm wondering if I could create a rank measure in DAX.)

Thanks!
Mike

 

[zestyphresh]

You can use something like the following,

=IF(NOT(ISBLANK([Sales])),
IFERROR(
CALCULATE(
VALUES(Table2[Year]),
TOPN(
1,
VALUES(Table2[Year]),
[Sales])),
0))

The above doesn't require the 3 measures you already have but instead uses the TOPN function to pick the best selling year. It uses a single SUM(Table(Sales)) measure (shown as [Sales] above) and then you will need a Year column in either your Fact Sales or Date Dimension table, it will work with either (just replace Table2[Year] with the correct reference). The '0' is what shows when it encounters an error, which in this measure will be when it finds more than one Year with the same total. If this is likely to happen you may need a better work around but generally speaking it may be so rare that it's not worth coding for. The reason i've used a 0 is that the Year field is numerical. If it is text you can change the 0 to a text string, say "Multiple Years" (with the quotes) or you will get a type mismatch error.

This should work with any extra filtering you add as well. I can't take any credit for this as I read about it on the PowerPivotPro blog a while ago!

 

[mikebutak]

You can use something like the following,

=IF(NOT(ISBLANK([Sales])),
IFERROR(
CALCULATE(
VALUES(Table2[Year]),
TOPN(
1,
VALUES(Table2[Year]),
[Sales])),
0))

The above doesn't require the 3 measures you already have but instead uses the TOPN function to pick the best selling year. It uses a single SUM(Table(Sales)) measure (shown as [Sales] above) and then you will need a Year column in either your Fact Sales or Date Dimension table, it will work with either (just replace Table2[Year] with the correct reference). The '0' is what shows when it encounters an error, which in this measure will be when it finds more than one Year with the same total. If this is likely to happen you may need a better work around but generally speaking it may be so rare that it's not worth coding for. The reason i've used a 0 is that the Year field is numerical. If it is text you can change the 0 to a text string, say "Multiple Years" (with the quotes) or you will get a type mismatch error.

This should work with any extra filtering you add as well. I can't take any credit for this as I read about it on the PowerPivotPro blog a while ago!

Wow, thanks zestyphresh! This looks like just the thing I needed.

: )
Mike