topic Re: Summary of duration "hh:mm:ss" total value in DAX Commands and Tips

Summary of duration "hh:mm:ss" total value

patkang — Wed, 09 Dec 2020 09:36:48 GMT

Hi everyone, I am still a novice in PBI and have run into some issues with the duration.

I am trying to aggregate the amount of time spent per user on a learning platform.

I formated the the data already into duration in the query editor

In the report tab, I used the following DAX expression to summarize the time spent per user in total

Total Duration = FORMAT(SUM('TABLENAME'[DURATION-HMS]),"hh:mm:ss")

Now it shows me the correct aggregation per user, but the grand total is not correct.

Thanks in advance, I appreciate your help!

Re: Summary of duration "hh:mm:ss" total value

patkang — Wed, 09 Dec 2020 10:15:45 GMT

Found the solution to my problem through the link below.

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=f500113d-799a-43bd-a432-63654ca6c75f&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

Re: Summary of duration "hh:mm:ss" total value

Fowmy — Wed, 09 Dec 2020 10:17:51 GMT

patkang

It should work for your total as well, what is the expected total?

If you can share some sample data, I will be able to check it out.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube LinkedIn

Re: Summary of duration "hh:mm:ss" total value

patkang — Wed, 09 Dec 2020 10:27:25 GMT

Fowmy I ended up using this code, as I had a separate column that showed me the total duration in seconds.

Total Duration = VAR TotalSeconds= SUM('UserActivity simple'[DURATION]) 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((Hors + (Days*24))<10,"0"&(Hors + (Days*24)),(Hors + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

Its weird since it seems not to work with the column that displays duration. How would you like me to share the sample data?