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
