Forum Discussion
Time totals
I have a table which records the number of times someone is late and the time. I need it to be a count of lates and then in the last column the total time for the rows counted.
Below is how it is appearing, each colour represents the same person, there should ideally be one entry for each person.
12 Replies
Hi, thank you but the 2nd column is the count of lates, I need the 3rd column to be the total amount of time late, for example, the first person was late 3 times, column 2 should show 3 lates and the last column should show 00:07:32 (Total time late of those 3 occasions)
hi Lakka00
Can you try this dax present in the post.
https://community.fabric.microsoft.com/t5/Desktop/Working-with-time-duration/m-p/3674090Or try this Dax
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)I hope I answered your question!
Hi Lakka00
Download PBIX file with the example below
This is done more easily in Power Query than in DAX. The DAX solution is very fiddly.
Reason being is your Late Length values will be treated as a time, rather than a duration which is what you want. DAX doesn't have the concept of a duration.
Power Query does. In PQ you can convert a time column to text, and then convert that to a duration.
Duplicate your data table, you can then group the rows by the Name and SUM the durations (note that I didn't enter the data exactly the same as yours so these Late Durations totals will be different to yours)
You've now got 2 tables. One with the original data and one with the total late duration calculation. Load these to PBI, create a relationship between the 2 based on Name.
This measure will give the Late Count
Count Lates = CALCULATE(COUNTROWS('DataTable2'), FILTER(ALL('DataTable2'), 'DataTable2'[Name] = SELECTEDVALUE('DataTable'[Name])))Regards
Phil
Hi, I ave tried this but I get an error in the sum or duration column.
I may be wrong but it doesn't seem to be converting to duration.
You can't add times. You need to convert the time column to text and then convert to duration.
Did you check my example file to see what I did?
Phil
