Answered by:
Max Sum Per Product within PowerPivot
Question

Hello,
I need to sum the max value per product. So for the value the table below it would return 13,134. That or I need to add a calculated column that would get the max value for each product.
Product_ID Amount 188395201 9,588 188395201 6,044 188395202 3,546 188395202 2,235
Thanks for the help
Thursday, February 19, 2015 10:53 PM
Answers

So, I think a little clarification would be in order.
I am not following "the max value per product" but expecting 13,134. 13,134 is the total of line 1 and 3. But you state "per product" which would make me think you are possibly looking for a. the total per product or b. the max aggregate total across all products (and possibly the product with the max aggregate.
For option a, you would just create a calculated field =SUM(Table[Amount]). This calculated field would then be calculated based on other context applied to the pivot table (i.e. with no product_id, it would return 38,181. With Product_ID as a row, it would calculate 15,632 for 201 and 5,781 for 202.
For option b, you would
1. create a calculated column [SumAmount] =SUMX( FILTER( Table1, Table1[Product_ID] = EARLIER( Table1[Product_ID]) ), [Amount] )
2. create a calculated field MaxSum:=CALCULATE(MAX([SumAmount]), ALL(Table1[Product_ID]))
3. create another calculated field that will find the product with the highest sum MaxProduct:=CALCULATE(MAXA(Table1[Product_ID]), FILTER(Table1, MAX(Table1[SumAmount])=[MaxSum]))
 Proposed as answer by Charlie Liao Thursday, February 26, 2015 3:23 AM
 Marked as answer by Charlie Liao Monday, March 2, 2015 2:43 PM
Monday, February 23, 2015 7:30 PM 
Hi powerpivotquestion,
I agree with PowerDAX that the requirement isn't completely clear but based on the result you're expecting (I'm assuming 13,134 is meant to be the total for the 2 products) something like this may be a solution:
Sum of Max Per Product:=
SUMX( ADDCOLUMNS( SUMMARIZE( 'Table1', 'Table1'[Product_ID] ), "MaxAmount", CALCULATE(MAX('Table1'[Amount])) ), [MaxAmount] )
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearn Edited by Michael Amadi Monday, February 23, 2015 9:04 PM
 Proposed as answer by Gerhard BruecklMVP, Editor Tuesday, February 24, 2015 6:28 PM
 Marked as answer by Charlie Liao Monday, March 2, 2015 2:43 PM
Monday, February 23, 2015 8:35 PM
All replies

Saturday, February 21, 2015 9:32 PMAnswerer

So, I think a little clarification would be in order.
I am not following "the max value per product" but expecting 13,134. 13,134 is the total of line 1 and 3. But you state "per product" which would make me think you are possibly looking for a. the total per product or b. the max aggregate total across all products (and possibly the product with the max aggregate.
For option a, you would just create a calculated field =SUM(Table[Amount]). This calculated field would then be calculated based on other context applied to the pivot table (i.e. with no product_id, it would return 38,181. With Product_ID as a row, it would calculate 15,632 for 201 and 5,781 for 202.
For option b, you would
1. create a calculated column [SumAmount] =SUMX( FILTER( Table1, Table1[Product_ID] = EARLIER( Table1[Product_ID]) ), [Amount] )
2. create a calculated field MaxSum:=CALCULATE(MAX([SumAmount]), ALL(Table1[Product_ID]))
3. create another calculated field that will find the product with the highest sum MaxProduct:=CALCULATE(MAXA(Table1[Product_ID]), FILTER(Table1, MAX(Table1[SumAmount])=[MaxSum]))
 Proposed as answer by Charlie Liao Thursday, February 26, 2015 3:23 AM
 Marked as answer by Charlie Liao Monday, March 2, 2015 2:43 PM
Monday, February 23, 2015 7:30 PM 
Hi powerpivotquestion,
I agree with PowerDAX that the requirement isn't completely clear but based on the result you're expecting (I'm assuming 13,134 is meant to be the total for the 2 products) something like this may be a solution:
Sum of Max Per Product:=
SUMX( ADDCOLUMNS( SUMMARIZE( 'Table1', 'Table1'[Product_ID] ), "MaxAmount", CALCULATE(MAX('Table1'[Amount])) ), [MaxAmount] )
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearn Edited by Michael Amadi Monday, February 23, 2015 9:04 PM
 Proposed as answer by Gerhard BruecklMVP, Editor Tuesday, February 24, 2015 6:28 PM
 Marked as answer by Charlie Liao Monday, March 2, 2015 2:43 PM
Monday, February 23, 2015 8:35 PM