cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Sum hours

Hello!

I need to sum hours (like in the pic) but I haven´t been able to develop it...

Total: 49:00

Anyone? 😃

Thanks!

1 ACCEPTED SOLUTION
Super User

Some variation of this should work for you, this formula converts the text below into minutes and divides by 60 to get hours.

This is a DAX custom column

=(LEFT([EST Block time],FIND(":",[EST Block time])-1)*60+RIGHT([EST Block time],LEN([EST Block time])-FIND(":",[EST Block time]))*1)/60

If you want it in the format below, you would have to have to do something like:

DAX

Custom Columns

[Hours]=LEFT([EST Block time],FIND(":",[EST Block time])-1)*1

[Minutes]=RIGHT([EST Block time],LEN([EST Block time])-FIND(":",[EST Block time]))*1)*1

Custom Field

Sum:=CONCATENATE(SUM([Hours])+ROUNDDOWN(SUM([Minutes])/60,0),":",MOD(SUM([Minutes]),60))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
7 REPLIES 7
Regular Visitor

Hi,
For those who need to have the result in this format but summarizing seconds. (you can either convert the time to seconds and it will work fine)
In order to Sum time in Seconds, converting it to the total sum in the format Hours:Minutes:Seconds
Where the Total Duration(s) is the Sum of the duration column in seconds

Total Duration(time) =
var Hi=divide([Total Duration(s)];(60^2))
var H=INT(Hi)
var Mi= (Hi-H)*60
var M = int(Mi)
var S = round((Mi-M)*60;0)
Return
If(H+M+S=0;
BLANK();
(H&":"&M&":"&S))

note: This formula will deliver the sum of seconds in the format:
HH:MM:SS
as the total number o hours like, p.e. 125h32m17s
where each 60seconds = 1min,
each 60min = 1hour
hour: Sum of the total hours.
Super User

Just to reassure: Say you'd end up with 48 hours and 45 minutes in total, should the result then be shown as

a) 48:45 or

b) 48:75

or wouldn't: 48.75 be more appropriate, as you're summing hours that will reach well above the standard time-notification, so this would then be the decimal representations of the sum of full hours?

In Excel, there is actually a time format that would deliver exactly the result in a): [h]:mm;@

But this option doesn't seem to be available in Power BI (at least I couldn't find the option to define your own formats).

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Super User

Some variation of this should work for you, this formula converts the text below into minutes and divides by 60 to get hours.

This is a DAX custom column

=(LEFT([EST Block time],FIND(":",[EST Block time])-1)*60+RIGHT([EST Block time],LEN([EST Block time])-FIND(":",[EST Block time]))*1)/60

If you want it in the format below, you would have to have to do something like:

DAX

Custom Columns

[Hours]=LEFT([EST Block time],FIND(":",[EST Block time])-1)*1

[Minutes]=RIGHT([EST Block time],LEN([EST Block time])-FIND(":",[EST Block time]))*1)*1

Custom Field

Sum:=CONCATENATE(SUM([Hours])+ROUNDDOWN(SUM([Minutes])/60,0),":",MOD(SUM([Minutes]),60))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

Hi @Greg_Deckler I have the same issue.

The column "TM" has the format HH: MM: SS with data type "text"

When placing the formula and replace the column "TM" shows error:

I am calculating the column "Time" with a value of minutes "Time = [Minutes] / 24/60" in hexadecimal to format it HH: MM: SS in the column "TM"

I need your help!!!

Super User

Not entirely certain what you are asking but your formula for T2 seems overly complex. If your TM data is always hh:mm:ss then use LEFT([column],2) to get your hours, RIGHT([column],2) to get your seconds and MID([column],4,2) to get your minutes. I think I have that correct.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

I need, sum "TM"

For example:

Resource       TM

Leslie             1:20

Leslie             3:39

Result:

Lestie: 4:59 hours

Memorable Member

Hmmm...Even in excel needs a trick..The only way I come up is to sum them but cannot show time format as the Date format for tthe measure is greyed out..So it will show 49,0 or assume that the sum of hours will be 15:45  the measure will show 15,75...

SumHours = SUMX( Table; Table[EST Block Time] * 24 )

Waiting also for other ideas..

Konstantinos Ioannou

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors