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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mbrierley
Helper III
Helper III

Average time appears as text

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.

 

Capture.PNG

4 REPLIES 4
AZJohnPowerBI
Helper I
Helper I

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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