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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lmatera
Frequent Visitor

Sum hours

Hello!

 

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

 

Captura.PNG

Total: 49:00

 

Anyone? 😃

 

Thanks!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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))

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
BrunoN
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. 
ImkeF
Super User
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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Greg_Deckler
Super User
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))

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler I have the same issue.

 

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

 

2017-12-05_13-18-07.jpg

 

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

 

2017-12-05_13-29-36.jpg

 

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"

 

2017-12-05_13-57-55.jpg

 

I need your help!!!

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.



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

I need, sum "TM"

 

For example:

 

Resource       TM

Adam            2:00

Leslie             1:20

Adam            5:42

Leslie             3:39

 

Result:

Adam: 7:42 hours

Lestie: 4:59 hours

konstantinos
Memorable Member
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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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