Scenario planning for purchasing from different suppliers

[Anonymous]

Hi,

I am trying to work out what total purchasing costs will be depending on which supplier I purchase from. This means that the purchase price isn't in the fact table like it would normally be in a calculated column. There is a different product price for each supplier.

So the tables I have are:

- a product table with each product in once, with columns for product category and size

- a price table linked to the product table with the price of each product for each supplier

- a purchase table linked to the product table for how much I need to purchase of each product

I'm wanting to do a row by row calculation as if it were a calculated column but in a measure, with the calculation being:

supplier's product price * quantity

Does anyone have any tips on how they have dealt with this issue before?

Many thanks,

Paddy

[ChandeepChhabra · Accepted solution]

@Anonymous 

Please see these results

Here is the PBIX you can Download

Hope this helps

Thanks

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[ChandeepChhabra]

@Anonymous - Quick Question - How would you decide the price when each supplier can have a different price for the product. So when you say

Cost = Qty * Price (which price is it?)

Unless you have the bifocation of purhcase quantity by supplier, there is ambiguity in solving this problem. 

[Anonymous]

Hi @Ashish_Mathur @ChandeepChhabra 

Many thanks for your replies. I am trying to see which supplier I should source certain groups of bottles of sauce from. The product groups are 'size' and 'recipe', so I want to see the impact of sourcing either a whole size of bottle from each supplier.

Fact table:

Product	Purchase Quantity
1	100
2	50
3	200
4	300
5	500
6	200
7	20

Product Table:

Product	Size	Recipe
1	125g	Lemon
2	125g	Sweet
3	125g	Hot
4	250g	Lemon
5	250g	Sweet
6	250g	Hot
7	500g	Lemon

Product Prices table:

Product	Supplier	Price
1	SUP1	£5.10
1	SUP2	£5.50
1	SUP3	£4.20
2	SUP1	£4.90
2	SUP2	£4.50
2	SUP3	£5.60
3	SUP1	£3.60
3	SUP2	£3.20
3	SUP3	£5.00
4	SUP1	£6.00
4	SUP2	£5.30
4	SUP3	£5.20
5	SUP1	£6.50
5	SUP2	£5.70
5	SUP3	£5.00
6	SUP1	£4.90
6	SUP2	£5.20
6	SUP3	£4.30
7	SUP1	£7.50
7	SUP2	£7.20
7	SUP3	£8.40

Then it would come to the pivot for the results by supplier and receipe/size

	SUP1		SUP2		SUP3
	Total Qauntity	Total Cost	Total Qauntity	Total Cost	Total Qauntity	Total Cost
Hot	400	£1,700.00	400	£1,680.00	400	£1,860.00
Lemon	420	£2,460.00	420	£2,284.00	420	£2,148.00
Sweet	550	£3,495.00	550	£3,075.00	550	£2,780.00

It is possible to merge the price and fact tables so that I have a calculated column like below, but this wouldn't be the best solution long term as I might add other elements to the data model such as calendar, and I would keep needing to merge tables together.

Product	Size	Product	Size	Recipe	Supplier	Price	Quantity	Total Cost
1	125g	1	125g	Lemon	SUP1	£5.10	100	£510.00
1	125g	1	125g	Lemon	SUP2	£5.50	100	£550.00
1	125g	1	125g	Lemon	SUP3	£4.20	100	£420.00

Any help would be much appreciated. Many thanks, Paddy

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur yes this works great, many thanks for your help, I appreciate it.

Paddy

[Ashish_Mathur]

You are welcome.

[ChandeepChhabra · Accepted solution]

@Anonymous 

Please see these results

Here is the PBIX you can Download

Hope this helps

Thanks

[Anonymous]

Hi @ChandeepChhabra , that works great, thank you for your help.

Paddy