topic Re: average time function in DAX Commands and Tips

average time function

lisaburton — Thu, 05 Jan 2023 10:43:20 GMT

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

Re: average time function

TOK — Thu, 05 Jan 2023 11:03:49 GMT

Hi lisaburton,
I would consider converting data type from time to duration.
Working with durations you can get an average as easy as usual.

If you need a tutorial on this topic, please let me know. Then I would upload one shortly.

Liked this post? More Content on:
https://www.youtube.com/channel/UC2lAgCgfyLCHsRv0h-ETBWQ

Re: average time function

tamerj1 — Thu, 05 Jan 2023 11:40:49 GMT

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"

Re: average time function

Anonymous — Thu, 05 Jan 2023 11:40:03 GMT

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