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 moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
93 | |
92 | |
84 | |
82 | |
49 |
User | Count |
---|---|
145 | |
142 | |
111 | |
71 | |
55 |