Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Talvien
Helper I
Helper I

Format the average of a duration

I have a column which shows me duration (DataType: Duration).

When inserting it into a table-visual it is always displayed as a double value (see right side of picture).

 

Unbenannt.JPG

 

Well, it is possible to format this value using FORMAT(value,"hh:mm:ss") in a CC, so it becomes like been seen on the left side of the picture.

 

Now the problem is, that this doesn't work, if I calculate the AVERAGE before. I can't do something like FORMAT(AVERAGEX(xxx:xxx);"hh:mm:ss").

 

Do you have any advices on this?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

This is something that I talked extensively with the Power BI product group about a couple weeks ago at the MVP Summit in Seattle. After a lot of discussion about the issue, I believe we finally settled on the correct owner and established a way to move forward. You can vote for the Idea here:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type

 

The big use case blocker is for call centers or anything like a call center where durations are critically important.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Talvien
Helper I
Helper I

Thanks for your answers. I already know @Greg_Deckler's solution but it doesn't work for me. No matter what I do, as soon as I try to use text formatting functions as "concat" or simple an "&", my whole table goes crazy.

I'm simply showing the duration in minutes as an integer now. Not really nice, but at least it works.
Just have the problem, that I want to calculate the average over all entrys of an item, but since the table contains dates and times for every item, the avarage is calculated for each occurence of the item on its own. Which is basically the same as ne normal runtime... I need to make this measure ignore the filter of the date and time columns.

Can you share some sample data and possibly how your table is laid out, the results you are getting and the results that you would like to get?

 

Without seeing the data and everything behind it, I would think that the solution would be to use an ALLEXCEPT clause in your measure calculation, so something like:

 

MyMeasure = CALCULATE(AVERAGE(Table[Duration]),ALLEXCEPT(Table[ItemID]))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Sure, my data in this table looks like this:

 

Unbenannt.JPG

 You can see that it is all the same item with differen runtimes. For the average I would like the average of all thes items in every line.

This:

Average Duration (min) = CALCULATE(AVERAGE('Job Execution History'[RunDurationMinutes]);ALLEXCEPT('Job Information';'Job Information'[JobName]))

is what the formula in the "Average Duration" column looks like. So it doesn't work 😕

 

 

 

dshah
Frequent Visitor

Hi Talvien

 

Below might help with averaging time duration

 

First convert the data into seconds.  Then divide the data by 86400 (60 min x 60 secs x 24 hours) to get output that can be converted to time.  

 

Then use below to get time equivalent 

FORMAT([seconds]/86400,"Long Time") (Note: You can divide it further to get the average time duration here)

 

This gives time equivalent with AM / PM at end.  You can use Left to trim it.  

Left(FORMAT([seconds]/86400,"Long Time"),7)

 

This solves the averaging problem and other time duration related problems. 

 

 

Capture.PNG

Additional Date/Time formats in DAX can be find below

https://technet.microsoft.com/en-us/library/ee634813(v=sql.105).aspx

 

Hope this helps.

 

Thanks

Well it's 2018 and we still don't have durations. Duration should be a fundamental data type and have suitable formatting options. If someting took one day, twenty hours, seventeen minutes and thirty two seconds I want to see it as 1d 20:17:32 not 1.84551

 

Equally I expect charts to be able to understand duration data points and display suitable axis values, and for values to allow automatic selection of average, min or max.

Greg_Deckler
Super User
Super User

This is something that I talked extensively with the Power BI product group about a couple weeks ago at the MVP Summit in Seattle. After a lot of discussion about the issue, I believe we finally settled on the correct owner and established a way to move forward. You can vote for the Idea here:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type

 

The big use case blocker is for call centers or anything like a call center where durations are critically important.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Here's a variant of the DAX from @Greg_Deckler et al as discussed in http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/bc-p/91032#M328   It uses Duration rather than number of seconds to calculate an Average.

 

Duration Average = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Steve Wheeler 22/11/2016 - converts from days (default format for Power Query's) Duration data type, rather than seconds
// * Given a number of days, returns a format of "d.hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = AVERAGE(Calls[Call Duration])

// The days will be the whole part of the Duration
VAR Days =
    INT (Duration)
// There are 24 hours in a day
VAR Hours =
    INT ( (Duration - Days) * 24)
// There are 60 minutes in an hour
VAR Minutes =
    INT  ( (Duration - Days - (Hours / 24) ) * 24 * 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP((Duration - Days - (Hours / 24) - (Minutes / (24 * 60)  ) ) * 24 * 60 * 60, 0)
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        Days, CONCATENATE (".", CONCATENATE (H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) ) )
    )

 

Change the column on which you calculate in the VAR towards the top and it should work in your table but, as text rather than numeric, can't be used as a chart axis or in other measures etc.  

 

Bring on that Duration data type...:)

 

th3h0bb5
Resolver II
Resolver II

The short answer is 'no', at least not with something that you can run calculations off of. In the background, duration values are saved as a fraction of a day (so think of some value is seconds divided by 86400 for the seconds in a day). However, when you format it to show as hh:mm:ss PowerBI interprets that as text and so you cannot aggregate.

 

You can see my similar question here and an article about how to get hh:mm:ss as a string value here. Also, if you have the value you want in seconds, you can format it correctly using this code (but you'll still run into the 'unable to aggregate' problem):

 

Time = 
FORMAT(INT(
IF(MOD([Seconds],60)=60,0,MOD([Seconds],60)) + 
IF(MOD(INT([Seconds]/60),60)=60,0,MOD(INT([Seconds]/60),60)*100) +
INT([Seconds]/3600)*10000), "0:00:00")

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.