Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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

Top Solution Authors