Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
I have what I think is a simple calculation, but I can't get it to work correctly.
I have a table with SKU items and efficiency rates. Efficiency rate is the expected duration of the job versus how long it actually took the technician to complete. I'm trying to calculate a success rate for each item - i.e., what percentage of jobs have a efficiency rate >= 1.
Data is by date, item name, work order ID. Here is mock data:
Date | Item_Name | Work_Order_Id | SKU_Efficiency |
1/1/2022 | Item 1 | Work Order 1 | |
1/2/2022 | Item 1 | Work Order 2 | 30% |
1/3/2022 | Item 2 | Work Order 3 | 50% |
1/4/2022 | Item 2 | Work Order 4 | 110% |
1/5/2022 | Item 3 | Work Order 5 | 80% |
1/6/2022 | Item 3 | Work Order 6 | 50% |
1/7/2022 | Item 3 | Work Order 7 | 120% |
1/8/2022 | Item 3 | Work Order 8 | 160% |
1/9/2022 | Item 3 | Work Order 9 | 100% |
1/10/2022 | Item 4 | Work Order 10 | 90% |
1/11/2022 | Item 4 | Work Order 11 | 80% |
1/12/2022 | Item 4 | Work Order 12 | 110% |
1/13/2022 | Item 4 | Work Order 13 | 120% |
1/14/2022 | Item 4 | Work Order 14 | 130% |
1/15/2022 | Item 4 | Work Order 15 | 60% |
1/16/2022 | Item 4 | Work Order 16 | 50% |
1/17/2022 | Item 5 | Work Order 17 | 0% |
1/18/2022 | Item 5 | Work Order 18 | 0% |
1/19/2022 | Item 5 | Work Order 19 | 90% |
1/20/2022 | Item 5 | Work Order 20 | 110% |
1/21/2022 | Item 5 | Work Order 21 | 120% |
1/22/2022 | Item 5 | Work Order 22 | 130% |
1/23/2022 | Item 5 | Work Order 23 | 140% |
1/24/2022 | Item 5 | Work Order 24 | 150% |
1/25/2022 | Item 5 | Work Order 25 | 40% |
1/26/2022 | Item 5 | Work Order 26 | 30% |
1/27/2022 | Item 5 | Work Order 27 | 140% |
1/28/2022 | Item 6 | Work Order 28 | 0% |
1/29/2022 | Item 6 | Work Order 29 | 80% |
1/30/2022 | Item 6 | Work Order 30 | 60% |
1/31/2022 | Item 6 | Work Order 31 | 110% |
2/1/2022 | Item 6 | Work Order 32 | 120% |
2/2/2022 | Item 7 | Work Order 33 | 40% |
2/3/2022 | Item 7 | Work Order 34 | 130% |
2/4/2022 | Item 7 | Work Order 35 | 120% |
2/5/2022 | Item 7 | Work Order 36 | 170% |
2/6/2022 | Item 7 | Work Order 37 | 160% |
2/7/2022 | Item 7 | Work Order 38 | 130% |
2/8/2022 | Item 7 | Work Order 39 | 60% |
2/9/2022 | Item 7 | Work Order 40 | 20% |
2/10/2022 | Item 7 | Work Order 41 | 950% |
2/11/2022 | Item 7 | Work Order 42 | 140% |
2/12/2022 | Item 7 | Work Order 43 | 140% |
2/13/2022 | Item 8 | Work Order 44 | 0% |
2/14/2022 | Item 8 | Work Order 45 | 90% |
2/15/2022 | Item 8 | Work Order 46 | 90% |
2/16/2022 | Item 8 | Work Order 47 | 110% |
2/17/2022 | Item 8 | Work Order 48 | 120% |
2/18/2022 | Item 8 | Work Order 49 | 130% |
2/19/2022 | Item 8 | Work Order 50 | 140% |
2/20/2022 | Item 8 | Work Order 51 | 150% |
2/21/2022 | Item 8 | Work Order 52 | 160% |
2/22/2022 | Item 8 | Work Order 53 | 170% |
I want to calculate a SKU Success Rate by Item
SKU Success Rate = Count of work orders for the item in which efficiency >= 1, excluding work orders where efficiency is 0.
Here are the results I expect:
SKU Success Rate | Count of EFF>=1 | Count of EFF > 0 | Success Rate |
Item 1 | 0 | 1 | 0% |
Item 2 | 1 | 2 | 50% |
Item 3 | 3 | 5 | 60% |
Item 4 | 3 | 7 | 43% |
Item 5 | 6 | 9 | 67% |
Item 6 | 2 | 4 | 50% |
Item 7 | 8 | 11 | 73% |
Item 8 | 7 | 9 | 78% |
Grand Total | 30 | 48 | 63% |
I've tried the following measure:
Solved! Go to Solution.
You can try three measures (or just one if you want to use variables)
Proud to be a Super User! | |
You can try three measures (or just one if you want to use variables)
Proud to be a Super User! | |
Thank you! I always forget to use CALCULATE with count. I appreciate it.