Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello!
I need to sum hours (like in the pic) but I haven´t been able to develop it...
Total: 49:00
Anyone? 😃
Thanks!
Solved! Go to Solution.
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,
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
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
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.
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
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..
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |