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

demid87

New Member
Joined
Nov 8, 2014
Messages
2
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.

DatePeriodPriceQuantity
1-Sep-201414100
1-Sep-20141520
1-Sep-20141730
1-Sep-201411030
1-Sep-201412050
1-Sep-201412550
1-Sep-201413050
1-Sep-201414020
1-Sep-201416080

<tbody>
</tbody>
















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.

DatePeriodPriceQuantity
1-Sep-2014100
1-Sep-20141420
1-Sep-20141410
1-Sep-20141710
1-Sep-201411030
1-Sep-201412510
1-Sep-201414020
1-Sep-201416020
1-Sep-201416015

<tbody>
</tbody>
















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.

DatePeriodPriceQuantity
1-Sep-20141470
1-Sep-20141520
1-Sep-20141720
1-Sep-201412050
1-Sep-201412540
1-Sep-201413050
1-Sep-201416045

<tbody>
</tbody>













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-known Member
Joined
Mar 16, 2014
Messages
1,263
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

New Member
Joined
Nov 8, 2014
Messages
2
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

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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 :)
 

Forum statistics

Threads
1,082,280
Messages
5,364,210
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top