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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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

@Anonymous , 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))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , 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))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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