Duration data as an average and hh:nn:ss

[wac1ldy]

Good day all,

I have the following data input:

Skill, Date, Calls, Time

Ed, 1/2/21, 10,37.00

Ed, 1/3/21, 12, 36.00

Ted, 1/1/21, 12, 7.00

Ted, 1/2/21, 11, 6.00

Fred, 1/1/21, 27, 12.00

Fred, 1/2/21, 26, 11.00

There are multiple rows by date, this is just a sample.

Time is imported as text. Converted in Query Editor to duration and a new column created for Duration.TotalSeconds([Time])

In the PBI desktop, the time shows as Whole Nuber (0 places)

I create a Matrix and pull in my data as shown all is good and I get the ability to show the time as an average per skill and filter by date, cool. Trouble is the Time is in seconds and I want it in h:nn:ss or nn:ss (management are not going to want to calculate what 365 seconds is, they want to see 6:05.

Everything I try to do formatiting as a date, when I pull it into the Matrix, it won't average. Excel, where are you when I need you?

I'm relatively new to PBI so any pointers you can give me would be appreciated.

Tx. CW

[wac1ldy]

Thanks guys. Great information and a great lesson for me. After reading your "pointers", what fixed it for me was building a measure with the average and the formatting in the right order. as always, I was overthinking it. CW

[d_gosbell · Accepted solution]

You can't do an average of a Time column, but you can do an average of a seconds column and then convert that to a time and format it as hh:nn:ss - the only thing to be aware of is that your average times need to be under 24hrs otherwise this technique will not work as there is no way to display the days (so 2 days and 7hrs would just show as 7hrs)

A measure that does this would look something like the following:

Avg Duration =

var avgDuration = AVERAGE('Table'[Duration])

var hours = INT( avgDuration / (3600) )

var minutes = int( (avgDuration - hours * 3600) / (60) )

var seconds = mod(avgDuration,60)

return time(hours, minutes, seconds)

[lbendlin]

use #time - PowerQuery M | Microsoft Docs or #duration - PowerQuery M | Microsoft Docs to create a new column in Power Query