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

[patkang]

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!

[Fowmy]

@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

[patkang]

@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? 

[patkang · Accepted solution]

Found the solution to my problem through the link below.

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=f500113d-...