Format the average of a duration

[Talvien]

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).

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?

[Talvien]

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.

[Greg_Deckler]

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]))

[Talvien]

Sure, my data in this table looks like this:

 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]

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. 

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

[Chris99]

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 · Accepted solution]

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.

[Anonymous]

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]

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")