Show results based on lowest values by selected attributes

[tweidner]

Hi all,

I'll try my best to get the scenario I'm up to explained in a simple way:

X amount of suppliers are submitting prices for x amount of products.

Not all suppliers submit prices for all products.

I want to select (or set) number of suppliers to be used and show prices for all products (no gaps).

SUPPLIER	MATERIAL	PRICE
ABC	111	1,00
ABC	222	1,50
ABC	333	2,00
DEF	111	1,20
DEF	222	1,00
HIJ	111	1,50
HIJ	333	1,80

Based on above table:

If I allow (select or set) only one supplier, it will return prices related to supplier ABC only.

If I allow two suppliers, it should return ABC/111, DEF/222, ABC/333

If I allow three supplier, it should return ABC/111, DEF/222, HIJ/333

I'm excited to see your ideas on this as it drives me crazy! 🙂

Thanks & Rgds
Tobias

[Anonymous]

OK. I'm gonna tell you what you have to do because we're not going to move any further with this if we carry on like that.

Prepare a simple model with:

1. 4 suppliers - A, B, C, D.
2. 6 materials - M1, M2, M3, M4, M5, M6.

Let only 1 supplier prepare prices for all materials. Let the prices for all the combinations of (supplier, material) be random but in such a way that they capture the core of the problem. Now, I want you to create in Excel some tables like this.

First table
Supplier | Material | Price

Second Table (will be several of them)
Cheapest Supplier | Material | Lowest Price

The first table will have all the combinations so that I can see them clearly. Make sure prices are small integers. The second one (and its versions according to the number of suppliers selected) will be the target table you want to see if you select 1 supplier, 2 suppliers, 3 suppliers.

Questions
1. What if there is no supplier that has prices for all the materials?
2. Why are you not able to formulate the rule which governs the selection of suppliers when a number has been selected?
3. This statement of yours

"When selecting "3" (suppliers), the formula should fill the table in the middle with the lowest prices from max 3 different suppliers, but with no gaps (blanks) for the 199 materials."

is not really helpful to establish the rule and code it. It may easily be that you can't find max 3 different suppliers that will give you the lowest prices for all the materials. All you could hope for is to select at most 3 different suppliers that would minimise the total of all the pricess across all the materials.

And this is why I need you to create an example of the calculation as described above.

Also, please make sure that when you paste the pictures of the tables THEY ARE READABLE. Because the pic in the private message is not.

I've had to answer you here because the number of private messages is limited and my limit has expired.

Thanks.

[tweidner]

can you access my PBI file: 

https://www.dropbox.com/s/rvb41hdvyz4mdm1/Cheapest%20Supplier%20Combination.pbix?dl=0

[daxer-almighty]

Hi @tweidner 

Sorry but I have to give up. The only info in the file I have is:

You are not giving me the information I need in order to solve this. You are not replying to my questions. You are not telling me what it actually means to give you an optimal set of n suppliers. You say "will identify the best combination." Best combination with respect to what criteria? I've been trying to get this info from you for some time now but you're not answering this crucial question.

Sorry but I have to quit.

Maybe someone else will be able to understand.

[Anonymous]

Do you mean something like this?

Let's say you've selected 2 as the number of suppliers. Let's also say you've got 5 suppliers and 3 products (just for the sake of argument). Then you'd like to know a 2-element subset of your suppliers that minimizes the total amount across all 3 products and which products would be supplied by which of the 2 suppliers.

Is this what you want to code?

[tweidner]

Yes, I think this comes close to what I'm looking for... Happy to have a quick TEAMS session (or similar) to show you my data.

many thanks !

[Anonymous]

By the way... When you say "close to what I'm looking for...", what is the difference? Can you please explain? I'm not able right now to have a Teams/Zoom session. If you don't want to optimize the set of suppliers based on the total of all products, then what would be the optimization condition?

[daxer-almighty · Accepted solution]

Is this what you were after?

[tweidner]

Thanks Daxer.

This solution will help me going forward. Any idea how this could be designed more flexible?

I.e. select a number of the suppliers to be used and result table will show combination which cheapest prices?

Best Regards

Tobias

[Anonymous]

@tweidner 

I have no idea how you would design a solution for this with just selecting the number of suppliers. To me it makes no sense right now but would be glad to hear and understand what you mean by this. Can you please explain in detail how you think something like this would work? I need a simple yet representative example. Thanks.

[Anonymous]

The examples do not define clear rules of the game, so giving you a good formula is not possible. You have to specify the assumptions of the model and the algorithm.

[tweidner]

Good Morning Daxer,

Did my explanation help? I'm really struggeling to get this issue solved...

Thanks & Rgds
Tobias

[Anonymous]

It looks to me as if you wanted:

1) Put selected products in a column (if no selection is made, all products will be put in there).

2) For each of the products get the cheapest supplier among those selected and their cheapest price. The cheapest supplier and their price will be 2 measures.

Is that correct?

Selecting the number of suppliers instead of the suppliers themselves makes no sense.

[tweidner]

Sorry for being not precise enough...

Setup:

Result:

If I select Supplier A and I select 2 suppliers to be awarded, it should look like this:

If I select Supplier C and I select 2 suppliers to be awarded, it should look like this:

Important to mention that prices for all (distinct) products are required.

Thanks, T