EXCLUDE certain amounts in calculation

[ConnieMaldonado]

I have the data below and am calculating efficiency rates for technicians performing various services onsite in customers' homes. 

The efficiency is calculated as:  EXPECTED Duration / ACTUAL Duration

Expected duration is provided for each item that is part of a work order ID.  Actual onsite duration is calculated based on geofence data (arrive time and depart time) using the address of the work order.  Since a single work order can have multiple items, if I sum actual onsite duration, I am overstating the amount.  In the example below (and in sample pbix), there are 3 items on the first work order.  The arrival time for the work order is 8:00 AM and depart time is 11:30 AM; thus, 210 minutes should be included only once in the efficiency calculation.

I can't come up with a calculation that will only count the ACTUAL Onsite Duration once for each work order.  I thought of adding a column that contained the count of work order IDs (for each work order), and then dividing the ACTUAL duration by that amount so that when summed, the amount would be correct.  Unfortunately, I couldn't figure out how to do that, either. 😀

I will be calculating efficiency by tech and eventually by Item no to find out which items the techs are struggling with (installing bunk beds or hanging a 60-inch TV, for example).

Any help would be appreciated!

Date	Work Order ID	Tech Name	Item No	Part No	Expected Onsite Duration MINUTES	GEOFENCE Arrive Time	GEOFENCE Depart Time	ACTUAL Onsite Duration MINUTES
1/1/2022	123AB	John Doe	Item 1234	Part 1	70	8:00 AM	11:30 AM	210
1/1/2022	123AB	John Doe	Item 234	Part 2	60	8:00 AM	11:30 AM	210
1/1/2022	123AB	John Doe	Item 209	Part 3	45	8:00 AM	11:30 AM	210
1/1/2022	859CD	John Doe	Item 309	Part 5	45	12:00 PM	2:00 PM	120
1/1/2022	859CD	John Doe	Item 453	Part 3	30	12:00 PM	2:00 PM	120
1/1/2022	783SD	John Doe	Item 908	Part 7	60	2:30 PM	4:00 PM	90
2/5/2022	456LK	Jane Doe	Item 789	Part 4	45	8:00 AM	10:30 AM	150
2/5/2022	456LK	Jane Doe	Item 345	Part 5	60	8:00 AM	10:30 AM	150
2/5/2022	366NB	Jane Doe	Item 345	Part 5	180	11:00 AM	3:30 PM	270
2/5/2022	458IE	Jane Doe	Item 565	Part 3	45	4:00 PM	5:00 PM	60

[ryan_mayu · Accepted solution]

@ConnieMaldonado 

maybe you can try this to create  a new column

Column = DATEDIFF('Table'[GEOFENCE Arrive Time],'Table'[GEOFENCE Depart Time],MINUTE)/CALCULATE(count('Table'[Work Order ID]),ALLEXCEPT('Table','Table'[Work Order ID]))

[ConnieMaldonado]

This works - thanks very much!

[ryan_mayu]

you are welcome