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 Guys,
Someone of this forum kindly wrote the below for me. This calculates the total time a person in making an external call. It works great but i an now trying to figure out how I can do an AVERAGE duration per day. For example - there were 10:51:50 (ten hours, 51 minutes and 50 seconds) of calls made on a particular day. There were 13 staff making these calls. The Average external call duration based on this would be 00:50:08 (50 minutes 8 secs).
I have a measure which counts the number of staff (see below). Is there a way to calculate the average?
Thanks
Count of Names = CALCULATE(counta(SQLNames[Full Name]))
--the below is the code to calculate the external calls duration
Total outbound time new measure = VAR Seconds1 = SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Out Time] ) ) VAR Minutes1 = SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Out Time] ) ) VAR Hours1 = SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Out Time] ) ) VAR Seconds2 = MOD ( Seconds1, 60 ) VAR Minutes2 = MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 ) VAR Hours2 = Hours1 + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 ) RETURN Hours2 & ":" & FORMAT ( Minutes2, "00" ) & ":" & FORMAT ( Seconds2, "00" )
Solved! Go to Solution.
@craigmon , Try like
Total outbound time new measure =
VAR Seconds = SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Out Time] ) + MINUTE ( 'Calls SQL'[Out Time] )*60 + HOUR ( 'Calls SQL'[Out Time] ) *3600 )
var Seconds1 = divide(Seconds,[Count of Names])
RETURN
quotient(Seconds1,3600 ) & ":" & FORMAT ( quotient(mod(Seconds1,3600 ),60), "00" ) & ":" & FORMAT ( mod(mod(Seconds1,3600 ),60), "00" )
This is amazing thank you so much
@craigmon , Try like
Total outbound time new measure =
VAR Seconds = SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Out Time] ) + MINUTE ( 'Calls SQL'[Out Time] )*60 + HOUR ( 'Calls SQL'[Out Time] ) *3600 )
var Seconds1 = divide(Seconds,[Count of Names])
RETURN
quotient(Seconds1,3600 ) & ":" & FORMAT ( quotient(mod(Seconds1,3600 ),60), "00" ) & ":" & FORMAT ( mod(mod(Seconds1,3600 ),60), "00" )
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |