Data allocation in retailer sales model

[blommethomas]

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

[Ashish_Mathur]

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.

[kentyler]

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.

[blommethomas]

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