Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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" )
User | Count |
---|---|
76 | |
74 | |
61 | |
61 | |
45 |
User | Count |
---|---|
108 | |
103 | |
93 | |
83 | |
64 |