Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
craigmon
Helper I
Helper I

Average call duration made per day

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" )

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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" )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
craigmon
Helper I
Helper I

This is amazing thank you so much

amitchandak
Super User
Super User

@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" )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.