How to rank multiple suppliers based on 3 measured values

[Gr8apmech]

With the communitie's help i have created a robust supplier dashboard to help the department manage supplier performance.  Now i need help to rank each supplier based on 3 different measures.  (defect per unit (DPU), On Time Delivery (OTD), and Spend $.  Adding spend will help us focus on the higher spend suppliers vs someone we spent $2.00 on and didn't come in on time.

Currently i am ranking using the following formula:

Ranking = RANKX(ALLSELECTED('ApprovedSupplierList'),[Performance],,DESC,Dense)

Performance = CALCULATE([Supplier OTD] - [DPU]*.0000000001)

My data looks like this sample:

Supplier	DPU	OTD	Spend
A	1000000	0.0%	$37,776.00
B	176470.5882	0.0%	$464,242.00
C	166666.6667	0.0%	$4,452.30
D	38461.53846	0.0%	$2,780,000.00
E	19047.61905	0.0%	$9,300.00
F	15267.17557	0.0%	$50,437.20
G	3333.333333	0.0%	$7,989.00
H	371.0575139	0.0%	$7,653.80
I	0	0.0%	$20.00
J	250000	25.0%	$70,170.00
K	43478.26087	25.0%	$47,907.98
L	0	25.0%	$7,583.00
M	0	45.3%	$123,761.88
N	13953.48837	46.2%	$394,198.97
O	500000	50.0%	$111,634.00
P	7299.270073	50.0%	$16.00
Q	3300.330033	50.0%	$23,510.80
R	0	50.0%	$13,822.00
S	52631.57895	77.8%	$15,660.00
T	4672.897196	78.6%	$67,939.96
U	9950.248756	80.0%	$20,337.68
V	0	80.0%	$2,005.83
W	0	81.3%	$6,883.60
X	52631.57895	83.3%	$6,024.62
Y	0	83.3%	$13,681.00
Z	0	83.3%	$20,326.70
AA	0	87.5%	$24,364.12
BB	0	88.7%	$308,800.05
CC	0	88.9%	$967.00
DD	0	88.9%	$1,490.05
EE	43165.46763	89.3%	$129,184.00
FF	0	89.5%	$20,075.72
GG	0	91.1%	$127,741.80
HH	0	91.3%	$163,576.24
II	0	91.7%	$19,000.13
JJ	0	92.3%	$15,462.00
KK	0	92.3%	$12,265.50
LL	333.8898164	92.5%	$126,174.08
MM	0	93.3%	$20,416.00
NN	0	93.8%	$5,708.60
OO	0	93.9%	$359,017.48
PP	77.65180929	94.9%	$150,715.63
QQ	91.46620324	96.0%	$319,649.38
RR	0	96.3%	$9,285.72
SS	0	96.5%	$79,017.56
TT	17142.85714	97.7%	$40,516.79
UU	0	98.6%	$21,029.97
VV	1500000	100.0%	$443,657.26
WW	66666.66667	100.0%	$2,774.25
XX	66666.66667	100.0%	$3,208,300.00
YY	62500	100.0%	$7,354.00
ZZ	0	100.0%	$7,190.00

I would like to see a ranking of each supplier based on the three collumns (DPU, OTD, Spend) in sequential order, no ties.

Supplier	DPU	OTD	Spend	Ranking
A	1000000	0.0%	$37,776.00	10
B	176470.5882	0.0%	$464,242.00	11

[Ashish_Mathur]

Hi,

Use the RANK function.  In the third argument ORDERBY, you can specify multiple measures that you want to rank by.

[lbendlin]

@Ashish_Mathur is correct, this is a much better approach. No need for a relation either.

You see the red squigglies - that is a bug in Power BI. ("Cannot find name") - you can ignore that

r = rank(DENSE,orderby('Table'[DPU],DESC,'Table'[OTD],DESC,'Table'[ Spend],DESC))

[Gr8apmech]

Getting an error on the first measure stating "Column 'DPU' in table 'Measure' can not be found or may not be used in this expression."

This is why i was using Rankx in my original formual.  Tried Rankx and it does not like the order of the variables (Dense, orderby, etc).  Other thoughts welcome!

[lbendlin]

Please don't name your tables "Measure". That is a reserved word.

[Gr8apmech]

Renamed table to Formulas and same result:

r = rank(DENSE,orderby('Formulas'[DPU],DESC,'Formulas'[Supplier OTD],DESC,'Delivery'[Line Total Amount],DESC))

Error "Column 'DPU' in table 'Formulas' cannot be found or may not be used in this expression."

[lbendlin]

What Power BI Desktop version are you using?  There were recent changes in the RANK function.

[Gr8apmech]

Version: 2.132.1053.0 64-bit (August 2024)

[lbendlin]

Are these columns actual columns or are some of them measures?

See attached - works for me with columns.

[Gr8apmech]

They are 3 different measures.

If i knew how, i would create a new table that captured these calculated values each month automatically.  Then i could use that table to perform the rank from?  does this sound do-able?

[lbendlin · Accepted solution]

Ah, that's the part I missed.  You cannot measure a measure. You need to materialize these before you can rank them.

Here is the materialized version

r2 = rank(DENSE,ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),[Supplier]),"DPU",CALCULATE([_DPU]),"OTD",CALCULATE([_OTD]),"Spend",CALCULATE([_Spend])),ORDERBY([DPU],desc,[OTD],desc,[Spend],desc))

[Gr8apmech]

BINGO!

Perfect solution sir!  You've helped me several times and I really appreciate your knowledge and experience.  

[lbendlin]

Thanks go to @Ashish_Mathur for suggesting to use RANK.

[Ashish_Mathur]

You are welcome. @lbendlin .  You deserve the kudos because you helped the OP all the way through.

[lbendlin]

MultiRank = 
var ms=maxx(all('Table (3)'[Spend]),[Spend])
var v = (sum('Table (3)'[DPU])+sum('Table (3)'[OTD]))*ms+sum('Table (3)'[Spend])
var t = addcolumns(allselected('Table (3)'[Supplier]),"v",calculate((sum('Table (3)'[DPU])+sum('Table (3)'[OTD]))*ms+sum('Table (3)'[Spend])))
return rankx(t,[v],v,DESC,Dense)