Select number of suppliers and return combination of lowest prices

tweidner — Fri, 17 Sep 2021 08:36:47 GMT

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)

Supplier	Product	Price
A	1	$ 8.40
A	2	$ 5.40
A	3	$ 0.10
A	4	$ 8.00
A	5	$ 5.30
B	2	$ 6.40
B	3	$ 1.90
B	4	$ 0.90
C	1	$ 5.70
C	2	$ 8.70
C	3	$ 9.00
C	4	$ 4.50

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

Supplier	Product	Price
A	1	$ 8.40
A	2	$ 5.40
A	3	$ 0.10
A	4	$ 8.00
A	5	$ 5.30

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

Supplier	Product	Price
A	1	$ 8.40
A	2	$ 5.40
A	3	$ 0.10
B	4	$ 0.90
A	5	$ 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

Re: Select number of suppliers and return combination of lowest prices

daxer-almighty — Sat, 18 Sep 2021 14:04:45 GMT

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.

topic Re: Select number of suppliers and return combination of lowest prices in DAX Commands and Tips

Select number of suppliers and return combination of lowest prices

Re: Select number of suppliers and return combination of lowest prices