Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Share some data and show the expected result.
@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.
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
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Ashish_Mathur yes this works great, many thanks for your help, I appreciate it.
Paddy
You are welcome.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |