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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jtcm4568
Helper I
Helper I

How to properly do average as New Column

Hello!

 

It's me again....lots of questions as I figure this all out.

I have a table full of information about the number of times someone answered the phone, and how many seconds that interaction took. It takes the following form:

DateTimeGroup NameNumber of CallsTotal length of calls in seconds
2021-01-01 08:00:00GroupA461,080
2021-01-01 08:00:00GroupB4127,211
2021-01-01 08:30:00GroupA12300

I first created a new Measure:

 

 

AverageHandleTime = DIVIDE(SUM([Total Length of Calls]),SUM([Number of Calls]),0)

 

 

That works wonderfully...but I just get the number of seconds, and I can't seem to find a way to represent that as a timestamp.

 

According to a few resources I found online, I have to create a 'Duration' column in my table. I've done that, as structured below:

DateGroupNumber of CallsLength of TimeAverage Call LengthDuration of Avg Call Length
2021-01-01 08:00GroupA461,080(1,080)/(46)=23.4700:00:23
2021-01-01 08:00GroupB4127,21117.5200:00:18

Now, however, if I put the numbers into a column chart, in order to get a "reasonable" number for my Y axis, I have to do an Average (of my Average). This feels wrong.

 

Is there an easier/better way to do duration that I'm missing? Or a better way to crack this nut?

 

Thanks!

Josh

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jtcm4568 , You should create a measure on top of this measure AverageHandleTime, and use that

 

time(quotient([AverageHandleTime],3600) , quotient(mod([AverageHandleTime],3600),60), mod(mod([AverageHandleTime],3600),60))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@jtcm4568 , You should create a measure on top of this measure AverageHandleTime, and use that

 

time(quotient([AverageHandleTime],3600) , quotient(mod([AverageHandleTime],3600),60), mod(mod([AverageHandleTime],3600),60))

This was brilliant, and for some reason, this kind of solution came up in exactly zero of my searched for how to figure out duration as a measure. Thank you!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.