locked
Max Sum Per Product within PowerPivot RRS feed

  • 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

    Monday, February 23, 2015 8:35 PM

All replies

  • 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

    Monday, February 23, 2015 8:35 PM