Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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" )
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
91 | |
86 | |
76 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |