Forum Discussion

ConnieMaldonado's avatar
ConnieMaldonado
Responsive Resident
4 years ago
Solved

EXCLUDE certain amounts in calculation

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!

 

 

DateWork Order IDTech NameItem NoPart NoExpected Onsite Duration MINUTESGEOFENCE Arrive TimeGEOFENCE Depart TimeACTUAL Onsite Duration MINUTES
1/1/2022123ABJohn DoeItem 1234Part 1708:00 AM11:30 AM                 210
1/1/2022123ABJohn DoeItem 234Part 2608:00 AM11:30 AM                 210
1/1/2022123ABJohn DoeItem 209Part 3458:00 AM11:30 AM                 210
1/1/2022859CDJohn DoeItem 309Part 54512:00 PM2:00 PM                 120
1/1/2022859CDJohn DoeItem 453Part 33012:00 PM2:00 PM                 120
1/1/2022783SDJohn DoeItem 908Part 7602:30 PM4:00 PM                   90
2/5/2022456LKJane DoeItem 789Part 4458:00 AM10:30 AM                 150
2/5/2022456LKJane DoeItem 345Part 5608:00 AM10:30 AM                  150
2/5/2022366NBJane DoeItem 345Part 518011:00 AM3:30 PM                  270
2/5/2022458IEJane DoeItem 565Part 3454:00 PM5:00 PM                   60

 

  • 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]))

3 Replies

  • 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]))