cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Trosa_220568
New Member

Calculate average time per day

Hi, how can I calculate the average process time in hours and minutes per day from the data below?

DateDealProcess Time
19/07/2021Deal 100:38
19/07/2021Deal 200:42
19/07/2021Deal 300:23
20/07/2021Deal 400:58
20/07/2021Deal 500:17
21/07/2021Deal 601:05
21/07/2021Deal 701:46
22/07/2021Deal 800:27
22/07/2021Deal 900:26
22/07/2021Deal 1002:49
22/07/2021Deal 1100:33
23/07/2021Deal 1201:15
23/07/2021Deal 1300:22
23/07/2021Deal 1400:39
23/07/2021Deal 1500:28

 

Thanks

3 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

@Trosa_220568 

You can create a measure as follows:

Average Process Time = FORMAT( AVERAGE( Table1[Process Time] ), "hh:mm:ss")

Fowmy_0-1627160507332.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

FrankAT
Community Champion
Community Champion

Hi @Trosa_220568 ,

with DAX you can do it like this. It looks a little bit weird but the time formating with DAX is buggy. 🤔

 

24-07-_2021_23-10-32.png

 

Average Process Time =
VAR _AverageTimeInDecimal =
    CALCULATE (
        AVERAGE ( 'Table'[Process Time] ),
        ALLEXCEPT ( 'Table', 'Table'[Date] )
    )
VAR _Hours =
    INT ( _AverageTimeInDecimal * 24 )
VAR _Minutes =
    INT ( ( _AverageTimeInDecimal - _Hours / 24 ) * 24 * 60 )
VAR _Seconds =
    INT ( ( _AverageTimeInDecimal - _Hours / 24 - _Minutes / 1440 ) * 24 * 3600 )
RETURN
    FORMAT ( _Hours, "00\:" ) & FORMAT ( _Minutes, "00\:" )
        & FORMAT ( _Seconds, "00" )

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

 

 

View solution in original post

v-easonf-msft
Community Support
Community Support

Hi,  @Trosa_220568 

If you want to calculate the average processing time in hours and minutes per day, you need to make some small adjustments to @CNENFRNL ‘s method.

When you specify the column to group by and the desired output, you need to select the Operation "Average" rather than  Operation "Sum".

11.png

Result:

12.png

Best Regards,
Community Support Team _ Eason

 

 

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hi,  @Trosa_220568 

If you want to calculate the average processing time in hours and minutes per day, you need to make some small adjustments to @CNENFRNL ‘s method.

When you specify the column to group by and the desired output, you need to select the Operation "Average" rather than  Operation "Sum".

11.png

Result:

12.png

Best Regards,
Community Support Team _ Eason

 

 

View solution in original post

FrankAT
Community Champion
Community Champion

Hi @Trosa_220568 ,

with DAX you can do it like this. It looks a little bit weird but the time formating with DAX is buggy. 🤔

 

24-07-_2021_23-10-32.png

 

Average Process Time =
VAR _AverageTimeInDecimal =
    CALCULATE (
        AVERAGE ( 'Table'[Process Time] ),
        ALLEXCEPT ( 'Table', 'Table'[Date] )
    )
VAR _Hours =
    INT ( _AverageTimeInDecimal * 24 )
VAR _Minutes =
    INT ( ( _AverageTimeInDecimal - _Hours / 24 ) * 24 * 60 )
VAR _Seconds =
    INT ( ( _AverageTimeInDecimal - _Hours / 24 - _Minutes / 1440 ) * 24 * 3600 )
RETURN
    FORMAT ( _Hours, "00\:" ) & FORMAT ( _Minutes, "00\:" )
        & FORMAT ( _Seconds, "00" )

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

 

 

View solution in original post

Fowmy
Super User
Super User

@Trosa_220568 

You can create a measure as follows:

Average Process Time = FORMAT( AVERAGE( Table1[Process Time] ), "hh:mm:ss")

Fowmy_0-1627160507332.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

CNENFRNL
Community Champion
Community Champion

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddA7DoQwDEXRrYxSI2E/J+RTzy4QxRR07L+GYNOEcRfpvEjWXdfAdaY8g8BhCt/9d3z6g6hJCdv0z6Ee4bioQ24HjR7VU3E8qXNW59GX7twoOZ7V46KO0Yvdlx2v5t5/pj5Ai9UbPAWtgLwG0BM5eYOnIbyBRZTqDawirsrbCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Deal = _t, #"Process Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Deal", type text}, {"Process Time", type duration}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Total", each List.Sum([Process Time]), type nullable duration}})
in
    #"Grouped Rows"

Screenshot 2021-07-24 221025.png

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!