Forum Discussion
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!
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))
7 Replies
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..
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).
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))
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!!!
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.
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)*60var M = int(Mi)var S = round((Mi-M)*60;0)ReturnIf(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.
