Skip to main content
cancel
Showing results for 
Search instead 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

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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