Calculate performance percentage based on 4 conditions

[Anonymous]

Hello everyone, would really appreciate your help!

I am struggling to get the right KPI becasue of a few conditions things resulting in infinite numbers.

Many thanks in advance )

Order Qty	Defect Qty	Performance%  (Defect Qty / Order Qty)
5	5
10	3
0	0
25	0

Conditions:

1. If Order Qty = Defect Qty then Performance = 0%

2. If  Order Qty >1 and Defect Qty = 0 then Performance = 100%

3. If Order Qty = 0 and Defect Qty = 0 then Performance = 0% (This is what gives the infinite numbers)

4. For any other case outside of first 3 conditions simply use the Performance Calculation = Defect Qty / Order Qty 

[Mariusz · Accepted solution]

Hi @Anonymous 

try this column

Performance Calculation = 
VAR __defect =  'Table'[Defect Qty] 
VAR __order = 'Table'[Order Qty]
RETURN 
SWITCH(
    TRUE,
    __defect = __order, 0,
    __defect = 0 && __order > 1, 1,
    DIVIDE( __defect, __order )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi Mariusz,

Thank you for your time & solution!

[Mariusz]

Hi @Anonymous 

No problem 🙂

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn