cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jorgast Member
Member

Sum of H:MM:SS

Hello Everyone,

I am new to Power BI and I am trying to figure out this problem. I have a field called [talk time] in Excel that is H:MM SS format. I brought the data into to Power BI and changed the format to HH:MM SS. What I am trying to do is sum the [talk time] data. When I just use the DAX of “SumTalkTime =Sum(TableName[talk time])” then I get 4:45:01 when it should be 28:45:01. How do I get the Power BI to show the correct sum in the HH:MM SS format?

 

Once I get that done I have more columns that are the same format. If I wanted to add [SumTalkTime] to another summed column let’s call it [SumTalk2], would I just use =Sum(SumTalkTime+ SumTalk2)”

 

As I looked through the forum, I tried to sum the talk time using just the normal sum function. Then I tried to go back to change it to the time format I need using the DAX formula below and I get a conversion error.

 

RevisedTT = (LEFT('Agent State Summary'[SumTalkTime],FIND(":",'Agent State Summary'[SumTalkTime])-1)*60+RIGHT('Agent State Summary'[SumTalkTime],LEN('Agent State Summary'[SumTalkTime])-FIND(":",'Agent State Summary'[SumTalkTime]))*1)/60

Cannot convert Value ‘12/31/1899 12’ to type text to type number

Here is some example data

Talk Time

Talk 2

TOTAL

4:11:37

4:11:37

8:23:14

3:41:17

3:41:17

7:22:34

4:02:22

4:02:22

8:04:44

5:24:39

5:24:39

10:49:18

3:47:59

3:47:59

7:35:58

2:54:36

2:54:36

5:49:12

4:42:31

4:42:31

9:25:02

28:45:01

28:45:01

57:30:02

1 ACCEPTED SOLUTION

Accepted Solutions
Jorgast Member
Member

Re: Sum of H:MM:SS

Thank you for the responses,

 

I have tried a DAX formula that seems to work.

SumTalkTime =

VAR TotalSeconds=SUMX('Table Name',HOUR('Table Name'[Column])*3600+MINUTE('Table Name'[Column])*60+SECOND('Table Name'[Column]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs = MOD(TotalSeconds,60)
return IF(DAYS=0,"",IF(DAYS>1,DAYS&"days ",Days&"day"))&IF(Hors<10,"0"&Hors,Hors)&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

5 REPLIES 5
Steve_Wheeler Established Member
Established Member

Re: Sum of H:MM:SS

Power BI doesn't handle Duration as a data type right now.  See this post for some discussion and sample DAX (for AVERAGE rather than SUM) that handles imported durations: https://community.powerbi.com/t5/Desktop/Format-the-average-of-a-duration/td-p/95322

 

And please vote to get this improved here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type

Super User
Super User

Re: Sum of H:MM:SS

You can do this in Power Query (Get Data) by adding a custom column and converting your time into decimal hours

 

= Time.Hour([TimeColumn]) + Time.Minute([TimeColumn])/24)



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Jorgast Member
Member

Re: Sum of H:MM:SS

Thank you for the responses,

 

I have tried a DAX formula that seems to work.

SumTalkTime =

VAR TotalSeconds=SUMX('Table Name',HOUR('Table Name'[Column])*3600+MINUTE('Table Name'[Column])*60+SECOND('Table Name'[Column]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs = MOD(TotalSeconds,60)
return IF(DAYS=0,"",IF(DAYS>1,DAYS&"days ",Days&"day"))&IF(Hors<10,"0"&Hors,Hors)&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

nikhilmanohar Regular Visitor
Regular Visitor

Re: Sum of H:MM:SS

I have similar situation but with one change. One of the columns has negative value in it. Your formula did work for 2 columns but gave an error for the negative value column. Any idea how to fix this?

 

Negative_Value_Error.JPG

 

Error_Screenshot.JPG

Jorgast Member
Member

Re: Sum of H:MM:SS

@nikhilmanohar

I know this may sound very basic, but i would just create a seperate column that checks for negative values. Something like -IF(Table[Column] < 0, Table[Column]*-1, Table[Column])

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 195 members 2,225 guests
Please welcome our newest community members: