The objective is to assist a Store Manager with computing potential sales across different products and colours. To start with let’s assume two datasets:
1. Customer-Colour dataset – a two column table which lists down the colour preference of each customer; and
2. Colour-Product-Price dataset – a three column table which lists down the multiple products associated with each colour and the associated prices
My objective is to compute the “Potential Sales” if each customer buys one unit of each colour of each product. While this can be resolved by using formulas (see Formula solution worksheet of the workbook), I’d like to resolve this problem with the help of the Power Query and PowerPivot tools.
The initial challenge will be to establish a relationship between the two tables because one cannot establish Many to Many relationships in a PowerPivot. The two tables above are a perfect example of Many to Many relationships because each customer likes multiple colours and each colour is associated with multiple products.
You may download my solution workbook from this link.
You may also view a short video of my solution here:
Compute potential Sales of a retail outlet
{ 8 Comments }