Forum Discussion

Talvien's avatar
Talvien
Icon for Helper I rankHelper I
9 years ago
Solved

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).     Well, it is pos...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    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.

th3h0bb5's avatar
th3h0bb5
Icon for Resolver II rankResolver II
9 years ago

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