CALCULATE Counts with Conditions

[ConnieMaldonado]

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:

SKU Success =

DIVIDE(

        COUNTAX(Table, Table[SKU_Efficiency] >= 1),

        COUNTAX(Table, Table[SKU_Efficiency] >= 0)

)

I have a table visual in which I include the Item_Name and was hoping th SKU_Success would be correct, but it's giving me wierd results.

This has to be easier 🙂  Thanks!

[jgeddes · Accepted solution]

You can try three measures (or just one if you want to use variables)

Success =

CALCULATE(

    COUNT(efficiencyTable[Item_Name]),

    FILTER(efficiencyTable, NOT(ISBLANK([SKU_Efficiency])) && [SKU_Efficiency] <> 0 && [SKU_Efficiency] >=1)

)

Total (Non-Zero) =

CALCULATE(

    COUNT(efficiencyTable[Item_Name]),

    FILTER(efficiencyTable, NOT(ISBLANK([SKU_Efficiency])) && [SKU_Efficiency] <> 0)

)

Success Rate =

DIVIDE(

    [Success],

    [Total (Non-Zero)],

    0

)

I ended up with the following result using your data.

[ConnieMaldonado]

Thank you!  I always forget to use CALCULATE with count.  I appreciate it.