Forum Discussion

lisaburton's avatar
lisaburton
Frequent Visitor
3 years ago

average time function

I am trying to gain an average 'tip time' of a lorry for last week and yesterday. I can't seem to be able to select the average the usual way, i only have the option to select the 'fastest' and 'ear...
average
DAX
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi lisaburton 
Please try the following measure

 

=
VAR AverageSeconds =
    AVERAGEX (
        'Table',
        VAR TipTime = 'Table'[Tip Time]
        RETURN
            3600 * HOUR ( TipTime )
                + 60 * MINUTE ( TipTime )
                + SECOND ( TipTime )
    )
VAR Hours =
    FORMAT ( QUOTIENT ( AverageSeconds, 3600 ), "00" )
VAR Minutes =
    FORMAT ( QUOTIENT ( MOD ( AverageSeconds, 3600 ), 60 ), "00" )
VAR Seconds =
    FORMAT ( MOD ( MOD ( AverageSeconds, 3600 ), 60 ), "00" )
RETURN
    Hours & ":" & Minutes & ":" & Seconds

You can also simplify as you don't have "Seconds"

=
VAR AverageMinutes =
    AVERAGEX (
        'Table',
        VAR TipTime = 'Table'[Tip Time]
        RETURN
            60 * HOUR ( TipTime )
                + MINUTE ( TipTime )
    )
VAR Hours =
    FORMAT ( QUOTIENT ( AverageMinutes, 60 ), "00" )
VAR Minutes =
    FORMAT ( MOD ( AverageMinutes, 60 ), "00" )
RETURN
    Hours & ":" & Minutes & ":" & "00"