Duration displayed in HH:MM:SS - Need to get average

[PeteS]

Hi all,

I have this column which shows the duration which a ticket was open for, in the format - HH:MM:SS
PBI sees this as a text field, i'm assuming because of the format.

In my visuals, I want to be able to show averages - however can't (I guess because it's a text field?)

How can I achieve this? Thank you!

[Anonymous · Accepted solution]

Hi @PeteS 

You can try the following solution

1.Add a column

Column = HOUR([Column1])*3600+MINUTE([Column1])*60+SECOND([Column1])

2.Create the following measures

Measure = AVERAGE('Table'[Column])

Average = var a = ROUNDDOWN([Measure]/3600,0)
var b=ROUNDDOWN(DIVIDE([Measure]-3600*a,60),0)
var c=[Measure]-3600*a-60*b
return FORMAT(TIME(A,B,C),"hh:mm:ss")

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chonchar]

Hi, 

See this post: 

https://community.fabric.microsoft.com/t5/Desktop/Calculate-average-time-per-day/td-p/1977255