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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FJ83
Helper II
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

 

FJ83_0-1652510601791.png

 

19 REPLIES 19
SpartaBI
Community Champion
Community Champion

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

SpartaBI_0-1652520474122.png

 




2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Hi @SpartaBI 

 

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

FJ83_0-1652547703500.png

 

 

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
SpartaBI
Community Champion
Community Champion

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

FJ83_0-1652550126359.png

 

This kind of result I'm expecting 

SpartaBI
Community Champion
Community Champion

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

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

SpartaBI
Community Champion
Community Champion

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

@SpartaBI 

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

Hi @SpartaBI @tamerj1 

 

please find the sample data below, 

 

FJ83_0-1652617891823.png

 

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?

429594E5-82AB-4835-8E7E-025B30EC0A06.png

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. 

 

 

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

@FJ83 

Create a new measure

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

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

SpartaBI
Community Champion
Community Champion

I agree 🙂

tamerj1
Super User
Super User

@FJ83 

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

No it's not giving the right solution

Hi @FJ83 

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

Arul
Super User
Super User

@FJ83 ,

I hope the below thread would help you,

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

Thanks,

Arul





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors