cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wac1ldy
Frequent Visitor

Duration data as an average and hh:nn:ss

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?

 

wac1ldy_0-1611172100486.png

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

Tx. CW

1 ACCEPTED SOLUTION
d_gosbell
Super User II
Super User II

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)

View solution in original post

3 REPLIES 3
wac1ldy
Frequent Visitor

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
Super User II
Super User II

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)

View solution in original post

lbendlin
Super User III
Super User III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.