# Max by dimension in a powerpivot table using DAX

• ### Question

• I hope my question is straight forward

I have a pp table with 2 columns (namely "Customer" and "amount").  As you can see, there can be multiple entries per customer.  I want to add a third column that shows the max of amount for each customer output on every row.  Is there a DAX implementation of this

Thanks

Fuzbe

Wednesday, October 26, 2011 6:35 PM

• If you want it inside a calculated column, supposing Customers is the name of your table, something like

=CALCULATE (MAX (Customers[Amount]), ALL (Customers), Customers[Customer] = EARLIER (Customers[Customer]))

should work fine. I have not tested it... just wrote it here. Thus, syntax errors might be there.

And... think twice at that ALL (Customers), it is required and understanding why is a good exercise. :)

Alberto Ferrari
http://www.powerpivotworkshop.com
• Proposed as answer by Friday, October 28, 2011 7:53 AM
• Marked as answer by Thursday, November 3, 2011 1:52 AM
Thursday, October 27, 2011 12:29 PM
• Alberto's formula is optimized for performance, hence a bit hard to understand without some deep DAX knowledge. If performance is not a concern, here is an equivalent formula that's easier to understand:

=MAXX(Filter(Customers, Customers[Customer] = EARLIER(Customers[Customer])), [Amount])

• Marked as answer by Thursday, November 3, 2011 1:52 AM
Friday, October 28, 2011 9:44 PM

### All replies

• If you want it inside a calculated column, supposing Customers is the name of your table, something like

=CALCULATE (MAX (Customers[Amount]), ALL (Customers), Customers[Customer] = EARLIER (Customers[Customer]))

should work fine. I have not tested it... just wrote it here. Thus, syntax errors might be there.

And... think twice at that ALL (Customers), it is required and understanding why is a good exercise. :)

Alberto Ferrari
http://www.powerpivotworkshop.com
• Proposed as answer by Friday, October 28, 2011 7:53 AM
• Marked as answer by Thursday, November 3, 2011 1:52 AM
Thursday, October 27, 2011 12:29 PM
• Hi Alberto,

Works perfectly...much appreciated.  Now I need to figure out why it works!

Thanks again!

Thursday, October 27, 2011 1:52 PM
• Alberto's formula is optimized for performance, hence a bit hard to understand without some deep DAX knowledge. If performance is not a concern, here is an equivalent formula that's easier to understand:

=MAXX(Filter(Customers, Customers[Customer] = EARLIER(Customers[Customer])), [Amount])

• Marked as answer by Thursday, November 3, 2011 1:52 AM
Friday, October 28, 2011 9:44 PM
• Hello,

Thank you for the query and it works great. I have 2 further questions and it will be great if you can help.

1) How can I find the value of related column? For example if we have item # in the above table as a column how can I find the max amount for the customer and at the same time the corresponding item #?

2) How to find out second max amount for each customer on every row?

Thanks again!

Thursday, November 10, 2011 3:48 PM
• 2) is easy, if the MaxAmount is the name of the first calculated column, simply add ([Amount] < EARLIER ([MaxAmount]) in the formula for the second one and you're done.

1) is harder, you need to take care of the fact that you might have more than one item# with the MaxAmount. Which version of PowerPivot are you using? With 2.0 it is slightly easier.

I would really love if you can send me a workbook with some data, I spend more time preparing the test set than writing the formula... :)

Alberto Ferrari
http://www.powerpivotworkshop.com
Thursday, November 10, 2011 9:57 PM
• Since the MAX() function only excepts a column reference how would I do the same using a calculated measure?
Wednesday, November 16, 2011 11:16 PM