Average time of the day

[Vsb79]

Hi,
How do you calculate average time in a column over a period of days selected.

I would need the average time which will be around 7:30 for the date range selected.
I used the FORMAT(AVERAGE(), ‘H:MM’) but it gives the correct answer when only one day is selected but if I select multiple days, it doesn’t give me the correct time.

Appreciate any input.

Thanks

[Harsh]

Hello @Vsb79,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned. Below are the DAX measures alongwith the screenshot of the final results provided for the reference -

1). First DAX measure is based on where results are achieved in a Text form in order to showcase the results based on “HH:MM” format. This result can be plotted in a Table or Matrix visualization.

Avg. Time - Harsh - Text Format = 
VAR _Total_Seconds = 
AVERAGEX( 
    Data , 
    HOUR( Data[Time] ) * 3600 + MINUTE( Data[Time] ) * 60 + SECOND( Data[Time] ) )

VAR _Days =
TRUNC(
    DIVIDE(
        DIVIDE(
            _Total_Seconds , 
            3600 , 
            0 ) , 
        24 ,
        0 ) )

VAR _Hours = 
TRUNC(
    DIVIDE(
        _Total_Seconds - _Days * 3600 * 24 ,
        3600 ,
        0 ) )

VAR _Minutes = 
TRUNC(
    DIVIDE(
        MOD(
            _Total_Seconds , 3600 ) , 
        60 ,
        0 ) )

VAR _Seconds = 
MOD(
    _Total_Seconds , 60 )

VAR _Results = 
IF( ISBLANK( _Total_Seconds ) , 
    BLANK() , 
    IF( _Days >= 1 , ( _Days * 24 ) + _Hours , _Hours ) & ":" & 
    IF( _Minutes < 10 , "0" & _Minutes , _Minutes ) )

RETURN
_Results

2). Second DAX measure is based on where results are achieved in a Numerical Format in order to plot the results in a graphical form.

Avg. Time - Harsh - Numerical Format = 
VAR _Total_Seconds = 
AVERAGEX( 
    Data , 
    HOUR( Data[Time] ) * 3600 + MINUTE( Data[Time] ) * 60 + SECOND( Data[Time] ) )

VAR _Days =
TRUNC(
    DIVIDE(
        DIVIDE(
            _Total_Seconds , 
            3600 , 
            0 ) , 
        24 ,
        0 ) )

VAR _Hours = 
TRUNC(
    DIVIDE(
        _Total_Seconds - _Days * 3600 * 24 ,
        3600 ,
        0 ) )

VAR _Minutes = 
TRUNC(
    DIVIDE(
        MOD(
            _Total_Seconds , 3600 ) , 
        60 ,
        0 ) )

VAR _Seconds = 
MOD(
    _Total_Seconds , 60 )

VAR _Results = 
IF( ISBLANK( _Total_Seconds ) , BLANK() , 
IF( _Days < 1 , ( _Days * 24 ) + _Hours + ( _Minutes / 60 ) ,
IF( _Days >= 1 , ( _Days * 24 ) + _Hours + ( _Minutes / 60 ) ) ) )

RETURN
_Results

I’m also attaching the working of the PBIX file and providing couple of links of threads below with the similar queries for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Calculation of Average Time - Harsh.pbix (66.0 KB)

Showing duration as HH:MM and subtotal

I’m trying to replicate this report: [image] And here’s my data: Hours Data.xlsx (12.4 KB) The hours column is the minutes worked. It doesn’t have to be DAX, Power Query is fine too. The issue I’m running into is the user wants the data displayed in HH:MM, but it needs to subtotal as well. I’ve tried having it in text format to get the “:” between, but that doesn’t sum. I’ve tried it as a decimal but turning it into a time-like number e.g. 7.25 into 7.15 to represent 15 minutes, but then…

[EnterpriseDNA]

Hello @Vsb79 ,

Did the response from @Harsh help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark his answer as the SOLUTION.

Thank you

[EnterpriseDNA]

Hello @Vsb79

Just following up if the response above help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

We’ve noticed that no response was received from you on the post above. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[Vsb79]

Sorry for the delay in response.
Thank you for the solution @Harsh Harsh, I did follow something similar and I did get the numerical format and was able to get the average of time.
Would there be a way to put time in Y axes? Not the numerical format. I could not figure that one out!

[EnterpriseDNA]

Hi @Vsb79

Thank you for your participation in our forum. We’re glad to hear that your first question or scenario has been resolved.

We will be closing this thread since your initial question has been addressed. If you have another question or scenario that you would like help with, we recommend creating a new forum post. This will allow our members and experts to provide more accurate and targeted solutions.

When creating a new post, please provide as much detail as possible like:

• A clear explanation of the problem you are experiencing
• A mockup of the results you want to achieve
• Your current work-in-progress PBIX file
• Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

This will help our members and experts understand your question and provide more effective solutions.

Thank you again for your participation, and we look forward to seeing your new post.

Best regards,