cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Anonymous
Not applicable

## Scenario planning for purchasing from different suppliers

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,

2 ACCEPTED SOLUTIONS
Impactful Individual

@Anonymous

Hope this helps

Thanks

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
Super User

Hi,

Share some data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Impactful Individual

@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
Not applicable

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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Impactful Individual

@Anonymous

Hope this helps

Thanks

Anonymous
Not applicable

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.