Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |