Display calculation in table at higher level than data grain

[ConnieMaldonado]

Hello!  I am calculating efficiency rates for field technicians who travel onsite to a client's home to perform various tasks.  The grain of my data is part number, and I essentially have data by date, technician, line of business, work order ID, Item Name, and Part number:

DATE	Tech ID	Line of Business	Work Order ID	Item Name	Part Number	Expected Duration Hours	Geofenced Duration Hours
2/1/2022	12345	LOB1	WO123	Table Assembly - End Table	Part123	0.5	1.9
2/1/2022	12345	LOB1	WO123	TV Stand or Media Storage Assembly - TV Stand	Part1234	0.75	1.9
2/1/2022	12345	LOB1	WO123	Storage Shelves Assembly	Part12345	0.5	1.9

The efficiency rate is Expected duration hours divided by the actual time onsite.  So if a technician was expected to spend an hour onsite to complete the items, and he actually spent 1.5 hours, efficiency would be 1/1.5 or 67% efficient.  Actual time onsite is measured by geofencing data based on the work order ID.    

My issue is that since the geofencing data is based on work order ID, and the grain of my data is part number, actual onsite time is overstated.  In the example above, onsite efficiency should be (.5 + .75 + .5) / 1.9 = 92%.  Instead, the onsite efficiency is calculated as (.5 + .75 + .5) / 5.7 = 31%.  

I would like to show the onsite efficiency at the work order level to all rows with the same work order number:  

DATE	Tech ID	Line of Business	Work Order ID	Item Name	Part Number	Expected Duration Hours	Geofenced Duration Hours	Onsite Efficiency
2/1/2022	12345	LOB1	WO123	Table Assembly - End Table	Part123	0.5	1.9	92%
2/1/2022	12345	LOB1	WO123	TV Stand or Media Storage Assembly - TV Stand	Part1234	0.75	1.9	92%
2/1/2022	12345	LOB1	WO123	Storage Shelves Assembly	Part12345	0.5	1.9	92%

Users want to see the components of the work order (i.e., Item Name and Part Number), but it doesn't make sense to calculate onsite efficiency at that level since you don't know how much of the onsite time was spent on each item.  I can live with showing 1.9 for each row (even though 1.9 is the total for the entire work order), but I would like to show efficiency at the work order level on each row.

I was able to calculate the correct onsite efficiency by adding a column called "COUNT WO" which I set equal to 1 for all rows and divided the sum of that into the sum of geofenced hours, but I am still not able to display the onsite efficiency by work order in the table (i.e., show 92% in the onsite efficiency column above).  I also think there must be a more eloquent way to do this. 

Here's my formula for onsite efficiency:

DIVIDE (

     SUM(Table[Expected Duration Hours]) ,

     DIVIDE (

          SUM(Table[Geofenced Duration Hours]),

          SUM(Count WO)

     )

)

Any help would be appreciated.  Thanks!

[v-zhangti · Accepted solution]

Hi, @ConnieMaldonado 

You can try the following methods.

Column:

Onsite Efficiency =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Expected Duration Hours] ),
        FILTER ( 'Table', [Work Order ID] = EARLIER ( 'Table'[Work Order ID] ) )
    ),
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Geofenced Duration Hours] ),
            FILTER ( 'Table', [Work Order ID] = EARLIER ( 'Table'[Work Order ID] ) )
        ),
        CALCULATE ( COUNT ( 'Table'[Work Order ID] ), ALLSELECTED () )
    )
)

Measure:

Measure = 
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Expected Duration Hours] ),
        FILTER (
            ALL ( 'Table' ),
            [Work Order ID] = SELECTEDVALUE ( 'Table'[Work Order ID] )
        )
    ),
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Geofenced Duration Hours] ),
            FILTER (
                ALL ( 'Table' ),
                [Work Order ID] = SELECTEDVALUE ( 'Table'[Work Order ID] )
            )
        ),
        CALCULATE ( COUNT ( 'Table'[Work Order ID] ), ALLSELECTED () )
    )
)

Does this meet your desired outcome?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ConnieMaldonado]

You are the best!  Thank you.

[Ashish_Mathur]

Hi,

What about this measure

= DIVIDE(SUM(Table[Expected Duration Hours]) ,MIN(Table[Geofenced Duration Hours]))

[Jos_Woolley]

Hi,

You don't say whether you're looking for a Calculated Column or a Measure.

Here's a Calculated Column:

Column =
VAR ThisWorkOrderID = 'Table'[Work Order ID]
VAR ExpectedDuration =
    CALCULATE(
        SUM( 'Table'[Expected Duration Hours] ),
        FILTER(
            ALL( 'Table' ),
            'Table'[Work Order ID] = ThisWorkOrderID
        )
    )
VAR ActualDuration =
    CALCULATE(
        MIN( 'Table'[Geofenced Duration Hours] ),
        ALLEXCEPT(
            'Table',
            'Table'[Work Order ID]
        )
    )
VAR Efficiency =
    DIVIDE(
        ExpectedDuration,
        ActualDuration
    )
RETURN
    Efficiency

Regards

[ConnieMaldonado]

Wow!  Didn't think of using ALLEXCEPT and calculating the minimum.  I actually need a measure; sorry I didn't mention that.

[ConnieMaldonado]

Let me see if I can figure out the measure based on what you've provided for the column

[Jos_Woolley]

Sure. Give me a shout if you get stuck!