Determine cheapest supplier and and calculate saving potential

[HDD009]

Good day,

after the good experience of my last post I will try it again with my next issue that I am working on.

Data looks like this:

Supplier	From	To	Transport Volume	Transport Cost	Week	Month	Year
Supplier A	North	South	1	401,48	1	1	2019
Supplier A	North	South	1	401,48	2	1	2019
Supplier B	North	South	1	525,68	1	1	2019
Supplier C	North	South	1	530,11	1	1	2019
Supplier D	North	South	1	540,28	1	1	2019
Supplier C	North	South	1	530,11	2	1	2019
Supplier A	West	North	1	222,22	1	1	2019

I have a list of suppliers on a already filtered corridor level (North to South) of Month 01 of Year 2019.

Supplier	From	To	Transport Volume	Transport Cost	Week	Month	Year
Supplier A	North	South	1	401,48	1	1	2019
Supplier A	North	South	1	401,48	2	1	2019
Supplier B	North	South	1	525,68	1	1	2019
Supplier C	North	South	1	530,11	1	1	2019
Supplier D	North	South	1	540,28	1	1	2019
Supplier C	North	South	1	530,11	2	1	2019

And what I want to show is this:

Supplier	From	To	Transport Volume	Total Costs	Unit Costs	Cheapest	Unit Costs Cheapest Supplier	Cost difference	Total Difference
Supplier A	North	South	2	802,96	401,48	Supplier A	401,48	0	0
Supplier B	North	South	1	525,68	525,68	Supplier A	401,48	124,2	124,2
Supplier C	North	South	2	1060,22	530,11	Supplier A	401,48	128,63	257,26
Supplier D	North	South	1	540,28	540,28	Supplier A	401,48	138,8	138,8
Total									520,26

Maybe someone of you knows how to solve this challenge.

[v-xicai]

Hi @HDD009 ,

You can create measure using DAX below.

Total Costs = SUM(Table1[Transport Cost])

Unit Costs = MAX(Table1[Transport Cost])

Cheapest = CALCULATE(MIN(Table1[Supplier]),FILTER(ALLSELECTED(Table1),Table1[From]=MAX(Table1[From])&&Table1[To]=MAX(Table1[To])&&Table1[Year]=MAX(Table1[Year])&&Table1[Month]=MAX(Table1[Month])))

Unit Costs Cheapest Supplier = CALCULATE(MIN(Table1[Transport Cost]),FILTER(ALLSELECTED(Table1),Table1[From]=MAX(Table1[From])&&Table1[To]=MAX(Table1[To])&&Table1[Year]=MAX(Table1[Year])&&Table1[Month]=MAX(Table1[Month])))

Cost difference = Table1[Unit Costs]-Table1[Unit Costs Cheapest Supplier]

Total Difference = SUM(Table1[Transport Volume])*Table1[Cost difference]

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETcmfLqFfXhJnL5yd1...

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HDD009]

Hi @v-xicai I think that's it.

There is only one thing:

Cheapest = CALCULATE(MIN(Table1[Supplier]),FILTER(ALLSELECTED(Table1),Table1[From]=MAX(Table1[From])&&Table1[To]=MAX(Table1[To])&&Table1[Year]=MAX(Table1[Year])&&Table1[Month]=MAX(Table1[Month])))

Shows only the min supplier (In this case it's sorting by the alphabet: A) not the cheapest supplier. What do i need to modify in this case?

[v-xicai · Accepted solution]

Hi @HDD009 ,

Try to change it in DAX below.

Cheapest = CALCULATE(MIN(Table1[Supplier]),FILTER(ALLSELECTED(Table1),Table1[From]=MAX(Table1[From])&&Table1[To]=MAX(Table1[To])&&Table1[Year]=MAX(Table1[Year])&&Table1[Month]=MAX(Table1[Month])&&Table1[Transport Cost]=Table1[Unit Costs Cheapest Supplier]))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.