Switch True() Leaving Blanks - Is there a better way?

[Gr8apmech]

My data is for supplier performance.  I have a measure to determine Defect per Unit (DPU) and a measure to determine Supplier On Time Delivery (OTD).  Using those 2 measures, i'm attempting to apply a scorecard which applies both color and 'text' to the table.  My problem is the Switch True method looks at the data in a very specific order whereas if my supplier has zero defect (DPU=0) and delivery is not on time (OTD) it is not considering the supplier as "Unsatisfactory".

Measure:

Supplier Rating = SWITCH(TRUE(),

[DPU]>10000&&[Supplier OTD]<0.98,"Unsatisfactory",

[DPU]>1000&&[DPU]<10000&&[Supplier OTD]>0.98&&[Supplier OTD]<0.985,"Marginal",

[DPU]>100&&[DPU]<1000&&[Supplier OTD]>0.9851&&[Supplier OTD]<0.995,"Fair",

[DPU]>10&&[DPU]<100&&[Supplier OTD]>0.9951&&[Supplier OTD]<0.999,"Excellent",

[DPU]<10&&[Supplier OTD]>0.9991&&[Supplier OTD]<=1,"Best in Class")

What my data looks like:

Supplier Rating	Supplier	DPU	Supplier OTD
Best in Class	A.H.	0	100.00%
Best in Class	Advan	0	100.00%
	Best	142857.1	100.00%
	Globa	166666.7	100.00%
	WEETE	166666.7	100.00%
	AT In	250000	100.00%
	TSI	250000	100.00%
	Sager	333333.3	100.00%
Unsatisfactory	Newar	13888.9	89.60%
	Amazo	1424.5	89.30%
Unsatisfactory	Imagi	35714.3	89.30%
	Austr	0	88.90%
	The B	0	88.90%
Unsatisfactory	Steve	333333.3	66.70%
	Sigma	0	50.00%
	Teled	0	50.00%
Unsatisfactory	ETS-L	250000	50.00%
Unsatisfactory	BAUGH	500000	50.00%
	Laco	0	33.30%
Unsatisfactory	NORMA	111111.1	33.30%
Unsatisfactory	Parag	500000	33.30%
	Rothe	0	30.10%
Unsatisfactory	Produ	74074.1	22.20%
	Cirri	0	20.00%
Unsatisfactory	Gretn	300000	20.00%
	DMC T	0	15.00%
Unsatisfactory	Moss	142857.1	14.30%
	Extre	0	8.30%
Unsatisfactory	Pelic	2000000	0.00%

What I would like to see:

All rows filled in with a Supplier Rating based on this 'scale' (I understand many of the 'supplier' do not meet the Marginal, Fair, Excellent categories yet):

Supplier Rating DPU OTD Dashboard Color

• 5. Unsatisfactory >10k <98% Red
• 4. Marginal 1,000-10K 98-98.5% Amber
• 3. Fair 100-1000 98.51-99.5% Yellow
• 2. Excellent 10-100 99.51-99.9% Green
• 1. Best in class <10 99.91-100% Blue

[v-kongfanf-msft]

Hi @Gr8apmech ,

Do you want a result similar to the following, you can use the following formula to create a calculated column.

Supplier Rating =
SWITCH (
    TRUE (),
    [DPU] > 10000
        && [Supplier OTD] < 0.98, "Unsatisfactory",
    [DPU] > 1000
        && [DPU] <= 10000
        && [Supplier OTD] >= 0.98
        && [Supplier OTD] < 0.985, "Marginal",
    [DPU] > 100
        && [DPU] <= 1000
        && [Supplier OTD] >= 0.9851
        && [Supplier OTD] < 0.995, "Fair",
    [DPU] > 10
        && [DPU] <= 100
        && [Supplier OTD] >= 0.9951
        && [Supplier OTD] < 0.999, "Excellent",
    [DPU] <= 10
        && [Supplier OTD] >= 0.9991
        && [Supplier OTD] <= 1, "Best in Class",
    "Unrated"
)

Best Regards,
Adamk Kong

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

[lbendlin · Accepted solution]

Change the order, and close the gaps.

Supplier Rating = 
SWITCH(TRUE(),
[DPU]<10&&[Supplier OTD]>0.999,"Best in Class",
[DPU]<100&&[Supplier OTD]>0.995,"Excellent",
[DPU]<1000&&[Supplier OTD]>0.985,"Fair",
[DPU]<10000&&[Supplier OTD]>0.98,"Marginal",
"Unsatisfactory")