cancel
Showing results for
Did you mean:
Helper II

How to find the average for each township

Hi,

They created the columns for "Enter_to_arrv_Seconds"(Response Time) and "Enter_to_disp_Seconds"(Hold Time). Just we need to convert the seconds' column to "mm: ss" format to find the average for each township. I will share the sample file here below

19 REPLIES 19
Resident Rockstar

@FJ83 just divide that column by 86400, make the data type of the result to Date/time and format it as nn:ss.

Showcase Report – Contoso By SpartaBI

Helper II

I did the changes in the column, How we can find the avg?

Since I used this column using the below DAX, however the data was not validating

Hold time in minutes =
VAR Hours = INT ( Duration / 3600)
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)
RETURN
Minutes*100+ Seconds
Resident Rockstar

@FJ83 hey, Average of that column? Average of a more complicated combination of that table that require AVERAGEX? You need to be more specific. I don't understand what you want to do.
Try to share the deisred result in the visual and what is the relevant data to get to that result.

Helper II

This kind of result I'm expecting

Resident Rockstar

@FJ83 every statist is represnted as a row in that table? and that tabla has a column with townships (or a dim table that filter that table with 1 to many)?
Then just created 2 measures:
Avg Hold = AVERAGE('Table'[Hold Time])
Avg Response = AVERAGE('Table'[Response Time]).

Put the township as a row or a column in a matrix and the measures as values.

Helper II

No, I'm placing the average for Hold time and Response time. The result should be "00:00" , still finding problem

Resident Rockstar

Then I don't understand what you are trying to do. Maybe @tamerj1?

Community Champion

still not clear.  I hope @FJ83 can provide a screenshot of the table. We might then be able to understand the average is based on which attribute(s).

Helper II

please find the sample data below,

Community Champion

Thank you @FJ83

Do you want the average of each column? In this screenshot, I guess by countywide you mean for the whole data but what is P1 and P2?

if you want the average of each column then simply AVERAGE(TableName[ColumnName])

You can store this average in a variable then you can convert to mm:ss format following either my method or @SpartaBI method. This should be simple. If not please let me what average are you trying to calculate. Maybe you can present some manual calculation sample to help us understand. Thank you for patience.

Helper II

P1 and P2 are called Priority, This comes from another column called PriorityKey

Community Champion

Create a new measure

``````Average Hold Time =
VAR HoldTime =
AVERAGE ( TableName[Enter_to_disp_Seconds] )
RETURN
QUOTIENT ( HoldTime, 60 ) & ":"
& MOD ( HoldTime, 60 )``````
Helper II

Yes, I used this calculation, however, I validate the data and let you know. Thanks to @tamerj1

Resident Rockstar

I agree 🙂

Community Champion

New Column >
Hold Time mmss =
VAR CurrentTime = 'Enter_to_disp_Seconds'[Hold Time]
RETURN
QUOTIENT ( CurrentTime, 60 ) & ":"
& MOD ( CurrentTime, 60 )

Helper II

No it's not giving the right solution

Community Champion

Hi @FJ83

Would you please share a screenshot showing the code and the results?

Post Prodigy

Thanks,

Arul

Helper II

We tried this one out! I used to declare the variable

VAR Duration = AVERAGEX(

But this leads to the wrong solution.

Announcements

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors