PowerPivot: Add one product to another

[eliwaite]

We are getting rid of some old products, and directing the demand for the old product to a new product. I am trying to setup a table which shows the change in demand for the new product. Here are some very simple tables which lays out what I am working with.

I have a table called SKU_Mapping which shows which products are being direct:

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD]Old Product ID[/TD]
[TD]New Product ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I have another table with some historical data called Sales:

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I also have another table which lists each SKU, called Master_SKU

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]Product ID[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I have the following relationships: 'Master_SKU'[Product ID] to 'Sales'[Product ID] and 'Master_SKU'[Product ID] to 'SKU_Mapping'[Old Product ID]

At the end of the day I would like a table like this

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Old Product ID[/TD]
[TD]New Product ID[/TD]
[TD]Old Quantity[/TD]
[TD]New Quantity[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

My problem is I cannot figure out how to write a formula to get the New Quantity. I am throwing mud at the wall but nothing is sticking. Any insights or advice would be greatly appreciated.

 

[Matt Allington]

How about you create a new column in your master sku table that has just the new mapping. If there is no change, then the new column is then same as the product id. If there is a change, they codes will be different. Then you can use either the new or old column in your reports.