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?
Solved! Go to Solution.
@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.
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.
Product Prices table:
Then it would come to the pivot for the results by supplier and receipe/size
|Total Qauntity||Total Cost||Total Qauntity||Total Cost||Total Qauntity||Total Cost|
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.
Any help would be much appreciated. Many thanks, Paddy
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.