Average Time with Filter / Measure

[bcampbell]

I have a measure that I am hoping to return in HH:MM;SS format instead of decimal.

The source value is currently in HH:MM;SS format

AVG DISP = CALCULATE(AVERAGE(rlmain[Time]),FILTER( rlmain, FIND( "DISP",rlmain[tencode],, 0) <> 0 ))

Is this possible ?

Thank You

[Nolock · Accepted solution]

Hi @bcampbell,

that's a little bit tricky. If you have a computed column, it is simple to change the data type and format of a column. But because a measure is not a part of a table, you can't do the same. But, you can use the function TIME(hour; minute; second) for transforming your AVERAGE value. The average value is a part of day.

The simple solution were:

CalculateAvgAsTime = TIME(0; 0; [CalculateAvgFrom] * 24 * 3600)

But it doesn't work, because Time expects max 32565 as value!!! Don't ask me why when a day has 86400 seconds. But I will create an issue for it.

My workaround is following:

CalculateAvgAsTime =
TIME ( [CalculateAvgFrom] * 24; 0; MOD ( [CalculateAvgFrom] * 24 * 3600; 3600 ) )

I use hours, ignore minutes and after that compute seconds in the hour with help of modulo.

[bcampbell]

Excellent That worked as needed!!!

but I was getting a syntax error using ; and instead used ,

[Nolock]

Great.

To , and ; - it depends what locale your system uses. Mine PowerBI/Excel/... expects semicollon.

[Nolock]

An issue is created: https://community.powerbi.com/t5/Issues/DAX-TIME-function-raises-an-exception-if-the-second-part-is-...

Btw. the max value of seconds you can currently use is 32 767 - I wrote a wrong value in my last post 😞