Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
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,

 

Paddy

2 ACCEPTED SOLUTIONS

@Anonymous 

Please see these results

 

Screenshot 2020-10-23 230153.png

 

Here is the PBIX you can Download

 

Hope this helps

Thanks

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ChandeepChhabra
Impactful Individual
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

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:

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

 

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

 SUP1 SUP2 SUP3 
 Total QauntityTotal CostTotal Qauntity Total CostTotal Qauntity Total Cost
Hot400£1,700.00400£1,680.00400£1,860.00
Lemon420£2,460.00420£2,284.00420£2,148.00
Sweet550£3,495.00550£3,075.00550£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.

ProductSizeProductSizeRecipeSupplierPriceQuantityTotal Cost
1125g1125gLemonSUP1£5.10100£510.00
1125g1125gLemonSUP2£5.50100£550.00
1125g1125gLemonSUP3£4.20100£420.00

Any help would be much appreciated. Many thanks, Paddy

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Paddy

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Anonymous 

Please see these results

 

Screenshot 2020-10-23 230153.png

 

Here is the PBIX you can Download

 

Hope this helps

Thanks

Anonymous
Not applicable

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

Paddy

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.