Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
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
Solved! Go to 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")
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.
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")
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |