locked
Max by dimension in a powerpivot table using DAX RRS feed

  • 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

Answers

  • 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 Challen Fu Friday, October 28, 2011 7:53 AM
    • Marked as answer by Challen Fu 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 Challen Fu 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 Challen Fu Friday, October 28, 2011 7:53 AM
    • Marked as answer by Challen Fu 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 Challen Fu 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