topic Re: How to find the average for each township in DAX Commands and Tips

How to find the average for each township

Anonymous — Sat, 14 May 2022 06:43:31 GMT

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

Re: How to find the average for each township

Arul — Sat, 14 May 2022 07:16:03 GMT

Anonymous ,

I hope the below thread would help you,

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Thanks,

Arul

Re: How to find the average for each township

tamerj1 — Sat, 14 May 2022 07:25:48 GMT

Anonymous

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

Re: How to find the average for each township

Anonymous — Sat, 14 May 2022 08:53:35 GMT

No it's not giving the right solution

Re: How to find the average for each township

Anonymous — Sat, 14 May 2022 08:59:37 GMT

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

VAR Duration = AVERAGEX(

CAD,DATEDIFF(CAD[IncidentDate],CAD[Enter_to_Disp_Seconds],second))

But this leads to the wrong solution.

Re: How to find the average for each township

SpartaBI — Sat, 14 May 2022 09:28:23 GMT

Anonymous 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

Re: How to find the average for each township

tamerj1 — Sat, 14 May 2022 09:43:19 GMT

Hi Anonymous

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

Re: How to find the average for each township

Anonymous — Sat, 14 May 2022 17:02:00 GMT

Hi SpartaBI

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 Duration = AVERAGEX(CAD,

DATEDIFF(CAD[IncidentDate],CAD[Hold Time],SECOND))

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

Re: How to find the average for each township

SpartaBI — Sat, 14 May 2022 17:36:52 GMT

Anonymous 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.

Re: How to find the average for each township

Anonymous — Sat, 14 May 2022 17:42:29 GMT

This kind of result I'm expecting

Re: How to find the average for each township

SpartaBI — Sat, 14 May 2022 17:46:45 GMT

Anonymous 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.

Re: How to find the average for each township

Anonymous — Sat, 14 May 2022 17:54:52 GMT

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

Re: How to find the average for each township

SpartaBI — Sat, 14 May 2022 17:59:19 GMT

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

Re: How to find the average for each township

tamerj1 — Sat, 14 May 2022 19:08:40 GMT

SpartaBI

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

Re: How to find the average for each township

SpartaBI — Sat, 14 May 2022 19:14:43 GMT

I agree 🙂

Re: How to find the average for each township

Anonymous — Sun, 15 May 2022 12:31:50 GMT

Hi SpartaBI tamerj1

please find the sample data below,

Re: How to find the average for each township

tamerj1 — Sun, 15 May 2022 12:47:34 GMT

Thank you Anonymous

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.

Re: How to find the average for each township

Anonymous — Sun, 15 May 2022 12:49:53 GMT

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

Re: How to find the average for each township

tamerj1 — Sun, 15 May 2022 12:55:07 GMT

Anonymous

Create a new measure

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

Re: How to find the average for each township

Anonymous — Sun, 15 May 2022 12:59:38 GMT

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