Forum Discussion
Data allocation in retailer sales model
Dear PowerBI Community,
I'm trying to build a sales model for a company which sell to supermarkets.
The goal is to calculate the net profit made each week, with net profit = Volume Sold x Price - Promotional Costs
I'm using 3 tables, each with an example of the dataset:
Products
| EAN CU | Name | Brand | Segment | Format | Price |
| 123456 | Soap Bar | CleanMe | Bars | 400gr | 0.5 |
| 345678 | XL Bar | CleanMe | Bars | 670gr | 0.65 |
Sales
| EAN CU | Week | Volume | Value |
| 123456 | 1 | 5.000 | 12.500 |
| 345678 | 4 | 3.200 | 17.000 |
Promotions
| Week | Brand | Segment | Format | Cost |
| 1 | CleanMe | Bars | null | 2.000 |
| 4 | null | Bars | 400gr | 3.500 |
Promotions can be on very specific segments or very broad ones:
- Line 1 is a promotion on all 'CleanMe Bars' regardless of the size
- Line 2 is a promotion on all '400gr Bars' regardless of the brand
I can easily link Sales & Products using EAN as a primary key.
But I'm struggling to connect the Promotions table:
I believe I should first create a calculated table which splits each promotion into a new row for every product involved.
For instance the promotion on 'CleanMe Bars' would need to be split over the 400gr and 750gr formats:
| Week | EAN | Cost |
| 1 | 123456 | 1.000 |
| 1 | 345678 | 1.000 |
After which I can link the combination Week + EAN to the Sales table.
But I don't see how I can create this new table using DAX or PowerQuery. Can somebody help me?
Bonus: preferably the cost allocated to each product is weighted according to % of total sales during the specified week)
KR,
Thomas
3 Replies
- kentyler
Solution Sage
I think you should split your promotions table into one table for segment promotions and one for brand promotions. Then each promotion table will have its own relationship and can be linked to sales or products or both...depending on what you want to report on.
Unfortunately this wouldn't make my model robust enough:
tomorrow I might want to split my promotions over a different variable (let's say size or flavour) and I would need to change the whole model.
What I'm looking for is a solution in Power Query (M language) but I struggle with the language semantics.
In pseudo-code I would need the following:
Promotions.AddColumn (list of EAN)
list of EAN = Contract.SelectRows(
if(Promotions.Brand is not null) Contract.Brand == Promotions.Brand
AND if(Promotions.Segment is not null) Contract.Segment== Promotions.Segment
AND if(Promotions.Format is not null) Contract.Format == Promotions.Format
)
Afterwards I can split the List into new Rows using 'Split Columns' and I can continue working on my model
- Ashish_Mathur
Super User
Hi,
Assuming there will always be an entry (no blanks there) in the Segment column of the Promotions Table, we can first bring over Brand from the Products Table into the Sales Table and then based on Week and Brand columns in the Sales table and Promotion table, we can bring over Cost from the Promotions Table into the Sales Table.
Hope this helps.
