Forum Discussion
Fault Percentage per Order
Hey,
I've got two tables that are interconnected by Order ID.
Table 1 contains all order information and Table 2 has 3 columns: Order ID, Fault Type and Fault Percentage. There can be multiple faults per order and the max sum of percentage adds up to 100.
Table 1
| Order ID | Date | Product | Customer |
| 132 | 01/01/2023 | Piston | Jake |
| 133 | 02/01/2023 | Gear Box | Lisa |
Table 2
| Order ID | Fault Type | Fault Percentage |
| 132 | Mechanical | 100 |
| 132 | Appearance | 0 |
| 133 | Mechanical | 0 |
I am looking for a solution that calculates the sum of the Fault Percentage (Mechanical + Appearance + other types) per each order. Then, if the fault percentage of an order is 0-40% then Green, 40-50% Amber and 50-100% Red.
Finally, I want to rank each product by Fault Percentage: my calculating the number of orders that are Red, Amber or Green vs total number of orders for that product.
What is the best approach to achieve this using measures? Thank you!
hummingbird
Add the order number from Table1 to a table visual and the following measure:Fault Measure = VAR __SumFault = SUM('Table2'[Fault Percentage]) VAR __Result = SWITCH( TRUE(), __SumFault <= 0.4 , "Green", __SumFault > 0.4 && __SumFault <= 0.5 , "Amber", "Red" ) RETURN __Result
3 Replies
hummingbird
Your Table1 has one product in your example per order, what if there are multiple products per order, then your relationship will break. if you have one-to-many.
More details required for your second question.Thanks. Let's assume there is only one product per order ID and we just need to know the sum of faults from the second table.
hummingbird
Add the order number from Table1 to a table visual and the following measure:Fault Measure = VAR __SumFault = SUM('Table2'[Fault Percentage]) VAR __Result = SWITCH( TRUE(), __SumFault <= 0.4 , "Green", __SumFault > 0.4 && __SumFault <= 0.5 , "Amber", "Red" ) RETURN __Result
