cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tweidner
Helper I
Helper I

Select number of suppliers and return combination of lowest prices

Good Morning PBI Community,

 

this is already my second attempt to get a solution for the following issue:

 

3 (or unlimited number)  suppliers are submitting prices for 5 (or unlimited number) products.

I'm looking for the lowest prices for the products, but the issue is that not all suppliers are able to provide prices for all products or that a combination of multiple suppliers would be more beneficial.

Idea was to use a filter (selected N records) to determine how many suppliers I would like to use.

 

Results should be:

eg. when selecting 2 (suppliers), the measure or table should return the lowest prices per product by using only 3 suppliers.

Gaps (in case only 1 supplier is selected and this supplier has not submmited prices for all products) can be left blank.

 

Here is how the fact table could look like: (in this example only supplier A has submitted prices for all products)

 

SupplierProduct Price 
A1 $           8.40
A2 $           5.40
A3 $           0.10
A4 $           8.00
A5 $           5.30
B2 $           6.40
B3 $           1.90
B4 $           0.90
C1 $           5.70
C2 $           8.70
C3 $           9.00
C4 $           4.50

 

Result selecting 1 (supplier): Only returns supplier A as this supplier has submitted prices for all products

 

SupplierProduct Price 
A1 $           8.40
A2 $           5.40
A3 $           0.10
A4 $           8.00
A5 $           5.30

 

Result selecting 2 (suppliers): should return max 2 distinct suppliers and lowest prices for all products

 

SupplierProduct Price 
A1 $      8.40
A2 $      5.40
A3 $      0.10
B4 $      0.90
A5 $      5.30

 

I think the key is to look at the lowest total of price calculated by selected N (of allowed suppliers).

 

Very excited to see your ideas!

 

Thanks & Rgds
Tobias

1 REPLY 1
daxer-almighty
Solution Sage
Solution Sage

Hi @tweidner 

 

I can see you're trying to set the optimization goal the way I suggested in the previous thread. However, it will not work. This optimization problem causes the so-called exponential explosion and hence it's not something that can be implemented in DAX (technically it can but not in practice). For even small to moderate model sizes, such a calculation will effectively never return.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!