Average time appears as text

[mbrierley]

We report on average working time and each month a report is generated from our finance team that is sent to me. This provides an average number of hours individuals have worked in a 17 week period. The format in for this column Excel is 'general'. All fine so far.

The problem comes when I put it into PowerBI. The data is displayed as text and any attempts I've made to change this produce an error ('we can't automatically convert the column to time'). Without being able to show the data as time, its not possible to analyse it properly i.e. we want to flag up anyone who is working over 48 hours. 

I've seen some similiar problems online involving dates, but nothing for time.

To note, where the time is 43:25, that is 25 minutes not 0.25 of an hour.

[AZJohnPowerBI]

If you're presenting Average Times in Days:Hours:Minutes:Seconds try the following:
Convert your excel chart to seconds (Usually =cell*86400)
Then make a new column in Power BI with the following

'Title of your column' =
//Days

Right("0"&INT('FACT Table Name'[Column Name]/86400),2)&":"

//Hours

&Right("0"&INT('FACT Table Name'[Column Name]/3600),2)&":"

//Minutes

&Right("0"&INT(('FACT Table Name'[Column Name]-INT('FACT Table Name'[Column Name]/3600)*3600)/60),2)&":"

//Seconds

&Right("0"&MOD('FACT Table Name'[Column Name],3600),2)

[v-shex-msft]

HI @mbrierley,

In fact, power bi not support to do aggregated on a date or time values.I'd like to suggest you write a measure formula to do some transform on your time values(e.g. total second or other numeric value) before aggregated on it. Then you can convert them back to time values after calculations.

Aggregating Duration Time Aggregating Duration Time 

Regards,

Xiaoxin Sheng

[mbrierley]

Sorry, but I'm not following you. I've looked at the link you posted, but that doesn't add anything to the data that I have as mine is already in that format (hh:mm - see photo). Are you saying that its impossible to order my data based on the highest values to the lowest?

[amitchandak]

@mbrierley , In power bi time , more than 24 hours not possible. add has number, display has text duration

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389