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:
<tbody>
</tbody>
I have another table with some historical data called Sales:
<tbody>
</tbody>
I also have another table which lists each SKU, called Master_SKU
<tbody>
</tbody>
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
<tbody>
</tbody>
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.
I have a table called SKU_Mapping which shows which products are being direct:
Old Product ID | New Product ID |
1 | 2 |
3 | 4 |
<tbody>
</tbody>
I have another table with some historical data called Sales:
Product ID | Quantity |
1 | 10 |
2 | 5 |
3 | 7 |
4 | 3 |
<tbody>
</tbody>
I also have another table which lists each SKU, called Master_SKU
Product ID |
1 |
2 |
3 |
4 |
<tbody>
</tbody>
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
Old Product ID | New Product ID | Old Quantity | New Quantity |
1 | 2 | 10 | 15 |
3 | 4 | 7 | 3 |
<tbody>
</tbody>
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.