# 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

• 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 Thursday, February 26, 2015 3:23 AM
• Marked as answer by 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(
SUMMARIZE(
'Table1',
'Table1'[Product_ID]
),
"MaxAmount", CALCULATE(MAX('Table1'[Amount]))
),
[MaxAmount]
)```

Regards,

Monday, February 23, 2015 8:35 PM

### All replies

• Saturday, February 21, 2015 9:32 PM
• 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 Thursday, February 26, 2015 3:23 AM
• Marked as answer by 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(
SUMMARIZE(
'Table1',
'Table1'[Product_ID]
),
"MaxAmount", CALCULATE(MAX('Table1'[Amount]))
),
[MaxAmount]
)```

Regards,