Forum Discussion
Helper IFormat 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).
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?
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.
8 Replies
- Greg_Deckler
Community Champion
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.
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
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")
TalvienThanks 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 :/
