PowerPivot: Subtract quantity only if date, period and price match between two tables

[demid87]

Hi,

I just installed powerpivot yesterday thinking that it might be able to solve a problem that I am facing.

Also, I am not sure whether the task I am looking at doing is even achievable in powerpivot.

I have two tables. Master and Sub. Both of which are hundreds of thousands of rows.

The objective is to compare Master and Sub, and subtract the quantity from master if the the date, period and price matches.

Master looks like the following:

Date can range from 1 sep to 30 sep.
Period can range from 1 to 48.
Price can be positive or negative and can be any price.
Quantity can be any positive number.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Period[/TD]
[TD]Price[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]25[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]40[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]60[/TD]
[TD]80[/TD]
[/TR]
</tbody>[/TABLE]
















And Sub looks like the following:

Date can range from 1 sep to 30 sep.
Period can range from 1 to 48.
Price can be positive or negative and can be any price.
Quantity can be any positive number.

In addition, there maybe multiple rows with the same date, period and price but different quantity.
There are prices that appear in master but do not appear in sub.
There are also multiple redundant price 0, quantity 0 rows in sub.
Quantity in sub may be less than or equal to master if the date, period and price match master.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Period[/TD]
[TD]Price[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]25[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]40[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]60[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]60[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]
















Objective table is as follows:

If quantity for any row is reduced to zero, ideally entire row is removed, but reducing quantity to zero should be fine as well.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Period[/TD]
[TD]Price[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]25[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]1-Sep-2014[/TD]
[TD]1[/TD]
[TD]60[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]













What I attempted so far:

I created a new column each for the master and sub tables by concatenating the date, period and price columns so as to come up with a column filled with 1-Sep-2014-1-4 kind of data. I wanted to use this column to establish a relationship between the master and sub tables. After which, I thought I might be able to just subtract the quantities away but it turns out I get an error when i try a = MASTER[Quantity]-Related(SUB[Quantity]).

Please advise.

Thank you.

 

[scottsen]

Well, this one is pretty unique Power Pivot can certainly do this.

1. What's the original source of your data? (Wonder if some SQL magic makes this all easier, or if we should include some Power Query in the mix)
2. This is JUST sept data... in the future does this get... uglier?
3. You want a row for each "unique key" in master -- where unique key is Date, Period and Price? (And rows from Sub that don't match your unique key... you just don't care about?)

There are a bunch of ways to do this, based on longer term goals, but for the sake of easy "auditing" lets do a calculated column on master. This does assume you don't care about rows from Sub... that aren't in master.

Without bothering with any relationships... (which would require some uniqueness)

=CALCULATE(SUM(Sub[Quantity]), Filter(Sub, Sub[Date] = Master[Date] && Sub[Period] = Master[Period] && Sub[Price] = Master(Price)))

Then you will have to like... "do something" w/ that calc column for your final results, but ... that should get you really close?

 

[demid87]

Hi Scott,

Thanks for you help. The formula works very well to get the quantity of SUB that matches the date, period and price. After which, i just did a Master[Quantity] - Master[CalcColumn] to get the remaining quantity.

With regards to 1,2,3:

1. The original data source are csv files that I append to each other every time new data comes in.
2. Actually, this data is running since a few years back and keeps coming in every day. No one in the office bothers to look at it since it's too troublesome to manipulate. The data doesn't get much uglier but the SUB table has already undergone some unwrapping to get it into the format above, the original format the data came in wasn't in a friendly format for pivoting. MASTER table data is used as is.
3. You are right, the rows in sub that aren't in master do not matter.

Now i can try to move on to the next step of the processing though I expect I'll probably bump into more problems very soon.

Thank you again.

 

[scottsen]

Cool, glad you are movin' forward. Your description of the data does make it sound... somewhat intermediate. You might be better served by massaging this data in Power Query, then bringing it in for further analysis to Power Pivot. But, I wouldn't stress it until you are seeing some good insight/value