Getting the average "minutes" in a column

[Anonymous]

Hey everyone, this is my first time posting in the Power BI community support and I'm just getting started with Power BI.

Can anyone help me solve this problem I have.

I have a column that has the time that a task took to complete in the format HH:MM:SS

so for example one row is this: 86:42:15 
82 Hours, 42 Minutes, 15 Seconds.

When I try to get the average for the column it gives me nonsense numbers and it's right I guess cuz it doesn't know what this data is. The format is in text for the moment. Can anyone help with the steps I need to achieve this task?

Thanks in advance

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Check the following formulas.

Column = 
var hours = MID('Table'[Column1],1,2)*60
var minutes = MID('Table'[Column1],4,2)*1
var seconds = MID('Table'[Column1],7,2)/60
var sum_ = hours+minutes+seconds
return
sum_

Column 2 = AVERAGE('Table'[Column])

Best Regards,

Jay

[mahoneypat]

Not straight forward, but you can put this formula in a custom column to get the total hours in decimal form that you can use for calculations.

= let

hours = Number.FromText(Text.BeforeDelimiter([TimeColumn], ":",0)),
minutes = Number.FromText(Text.BetweenDelimiters([TimeColumn], ":",":")),
seconds = Number.FromText(Text.AfterDelimiter([TimeColumn], ":",1))
in
Duration.TotalHours(#duration(Number.RoundDown(hours/24,0), Number.Mod(hours, 24), minutes, seconds))

Regards,

Pat

[AlB]

Hi @Anonymous 

What data type is the column, text?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers