locked
Powerpivot and conditional sum RRS feed

  • Question

  • I'm relatively new to powerpivot and I'm trying to leverage it in order to make this scenario.

    I have a table with these fields: Date, Customer, Destination, Weight. I've already set up powerpivot in order to choose a price for weight using a banding system. So if weight is > and < of a certain amount, it takes a specific price. This is the first scenario.

    My problem is that I want to make a more advanced scenario. I want to tell powerpivot to take the records where Date, Customer and Destination are the same, sum the weight, choose the right price with the new weight and then multiply the price for the weight. And of course for all the records without same "Date, Customer and Destination" fields the banding and price multiplication should work like the first scenario.

    Thanks for your help.

    Sunday, June 10, 2012 3:21 PM

Answers

  • Thank's for your help.

    Your formula works and it's what I was looking for. But it works with this slightly changed syntax on my powerpivot:

    =SUMX(FILTER(Table1,Table1[Date]=EARLIER(Table1[Date]) &&
                              Table1[Customer]=EARLIER(Table1[Customer]) &&
                              Table1[Destination]=EARLIER(Table1[Destination]) 
                  ), 
                Table1[Weight] 
        )

    Thank's again.

    • Marked as answer by Elvis Long Monday, June 18, 2012 1:20 AM
    Sunday, June 17, 2012 10:57 AM

All replies

  • Step 1: Get the total weight for the current Date, Customer & Destination by creating a calculated column called AggWeight:

    =SUMX(FILTER(Table1,Table1[Date]=EARLIER(Table1[Date]),
                              Table1[Customer]=EARLIER(Table1[Customer]),
                              Table1[Destination]=EARLIER(Table1[Destination]) 
                  ), 
                Table1[Weight] 
        )

    Step 2: Pull in the Price for AggWeight using the same banding system you described.

    Step 3: Multiply the original Weights by the Price




    • Edited by ruve1k Monday, June 11, 2012 3:28 AM
    • Proposed as answer by Elvis Long Sunday, June 17, 2012 10:54 AM
    Monday, June 11, 2012 3:27 AM
  • Thank's for your help.

    Your formula works and it's what I was looking for. But it works with this slightly changed syntax on my powerpivot:

    =SUMX(FILTER(Table1,Table1[Date]=EARLIER(Table1[Date]) &&
                              Table1[Customer]=EARLIER(Table1[Customer]) &&
                              Table1[Destination]=EARLIER(Table1[Destination]) 
                  ), 
                Table1[Weight] 
        )

    Thank's again.

    • Marked as answer by Elvis Long Monday, June 18, 2012 1:20 AM
    Sunday, June 17, 2012 10:57 AM