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
Hi All,
it is my first post as i have some difficulties to find an answer to my issue.
I am a service desk manager and i'am working on a power BI dashboard to replace the excel file i am using until now.
My issue is that when i want to add times, can have the sum when times are in numeric format. in date/time format i cannot have the sum only the min or max value :
here's a sample :
Day HHMM Entrants Abandon_15s Servis Servis_30s Talk time
16/10/2018 08:00 2 0 2 2 00:06:58
16/10/2018 09:00 5 0 5 5 00:09:33
16/10/2018 09:30 2 0 2 1 00:20:40
16/10/2018 10:00 4 0 4 4 00:02:31
16/10/2018 10:30 3 0 3 3 00:18:40
16/10/2018 11:00 1 0 1 1 00:09:43
16/10/2018 11:30 3 0 3 3 00:12:24
16/10/2018 13:30 3 0 3 3 00:04:56
16/10/2018 14:00 2 0 2 2 00:07:25
16/10/2018 15:00 1 0 1 1 00:03:16
16/10/2018 16:00 4 0 4 4 00:11:33
16/10/2018 16:30 1 0 1 1 00:17:39
my goal is to have the sum of talktime for a given day in order to calculate the average time of talk time.
formula is talktime/answered calls. issue is that as my data are sliced into halfs hours i get an error (multiple values for a given day)
question is how can i add the times for a day ?
thank you in advance for your help, and sorry if my english is not perfect, its not my native language
Solved! Go to Solution.
Hi @Captain_Ricard,
There could be two solutions. Please check out the demo in the attachment.
1. Add a column and convert the times into seconds which is numeric values. The calculation could be easy.
2. Integrate the solution 1 in a measure.
Measure = VAR avg_seconds = DIVIDE ( SUMX ( 'Table1', DATEDIFF ( TIME ( 0, 0, 0 ), [Talk time], SECOND ) ), COUNT ( Table1[Abandon_15s] ), 9999 ) VAR hours = INT ( DIVIDE ( avg_seconds, 3600 ) ) VAR minutes = INT ( DIVIDE ( MOD ( avg_seconds, 3600 ), 60 ) ) VAR seconds = INT ( MOD ( MOD ( avg_seconds, 3600 ), 60 ) ) RETURN hours & ":" & minutes & ":" & seconds
Best Regards,
Dale
Hi @Captain_Ricard,
There could be two solutions. Please check out the demo in the attachment.
1. Add a column and convert the times into seconds which is numeric values. The calculation could be easy.
2. Integrate the solution 1 in a measure.
Measure = VAR avg_seconds = DIVIDE ( SUMX ( 'Table1', DATEDIFF ( TIME ( 0, 0, 0 ), [Talk time], SECOND ) ), COUNT ( Table1[Abandon_15s] ), 9999 ) VAR hours = INT ( DIVIDE ( avg_seconds, 3600 ) ) VAR minutes = INT ( DIVIDE ( MOD ( avg_seconds, 3600 ), 60 ) ) VAR seconds = INT ( MOD ( MOD ( avg_seconds, 3600 ), 60 ) ) RETURN hours & ":" & minutes & ":" & seconds
Best Regards,
Dale
@v-jiascu-msft Dale
The Measure works well !
Here's a test :
The measure is the column named Temps Moyen HHMM
Thank you again for you help
Have a nice day
Hi @Dale
I tried to convert into numerioc values, but in the report view the format still in numeric and not so "understandable", when i convert the column into HH:MM format there is no calculation, only the choice of min and max as stated before.
So il will try the second solution you give and build a measure and revert back here to tell you the results
thank you for the tip and the attached example !
regards
Hi @Captain_Ricard,
I forgot to add the details. It's a new column with the formula.
Seconds = DATEDIFF(time(0, 0, 0), [Talk time], SECOND)
Best Regards,
Dale
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 |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
106 | |
77 | |
66 | |
65 |