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 'earliest'

Any ideas if there is a Dax code that could get an average time?

 

Many thanks 

Lisa 

 

average
DAX

3 Replies

  • 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"

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago
    In PowerQuery, Duplicate the time column and convert the data type to Decimal.
    Let's say your new column is "TimeFieldinDecimal"
     
    DAX:
    Avg Time (Time) =

    var _duration = Avg(TimeFieldinDecimal)
    var _hrs = _duration * 24
    var _mins =  (_hrs - int(_hrs)) * 60
    var _sec =  (_mins - int(_mins)) * 60

    return FORMAT(int(_hrs), "#00") & ":" & FORMAT(int(_mins), "#00") & ":" & FORMAT(int(_sec), "#00")