Forum Discussion

ConnieMaldonado's avatar
ConnieMaldonado
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

Display calculation in table at higher level than data grain

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 dat...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

Jos_Woolley's avatar
Jos_Woolley
Icon for Solution Sage rankSolution Sage
4 years ago

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's avatar
ConnieMaldonado
Icon for Responsive Resident rankResponsive Resident
4 years ago

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

  • ConnieMaldonado's avatar
    ConnieMaldonado
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

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

     

    • Jos_Woolley's avatar
      Jos_Woolley
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Sure. Give me a shout if you get stuck!