cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

 DateTime Group Name Number of Calls Total length of calls in seconds 2021-01-01 08:00:00 GroupA 46 1,080 2021-01-01 08:00:00 GroupB 412 7,211 2021-01-01 08:30:00 GroupA 12 300

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:

 Date Group Number of Calls Length of Time Average Call Length Duration of Avg Call Length 2021-01-01 08:00 GroupA 46 1,080 (1,080)/(46)=23.47 00:00:23 2021-01-01 08:00 GroupB 412 7,211 17.52 00: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
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))

2 REPLIES 2
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))

Helper I

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors