Forum Discussion

NB689's avatar
NB689
Helper I
4 years ago
Solved

Enabling Time Measure to Average

Hello,

 

I would like to show average time logged in for a group of users based on data that looks like the below table:

 

User IDLogin TimeLogout Time
A1:00 PM2:30 PM
B8:00 AM9:00 AM
B11:00 AM12:00 PM
C12:00 PM1:00 PM
C2:00 PM3:00 PM
C3:30 PM4:00 PM

 

I plan to create a card that shows the "Average Time Logged In". The average for this would be the total amount of time logged into the system divided by the total number of Users. The format that I am looking to achieve is "2 hr: 0M" (based on a total of 6 hours logged in, divided by 3 users).

 

Thank you

 

 

 

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi NB689 
    Actually dividing by the number of days will not solve the problem. In the sample data that I have the problem with that approach was clear. All the days have a duration of less than one day. If you divide by the number days the result of the total will be 20min instead of 2hr. You may try yourself. In the following comment will post the correct number of days formula just for reference.


    I hope this code will work as expected

    Average Time Logged In Per User Per Day = 
VAR AverageTime =
    AVERAGEX (
        VALUES ( Sheet1[Full Name and 3-4] ),
        CALCULATE ( 
            SUMX (
                Sheet1,
                VAR Login = Sheet1[Login]
                VAR Logout = Sheet1[Logout]
                VAR Minutes =
                    DATEDIFF ( Login, Logout, MINUTE )
                VAR Days =
                    DATEDIFF ( Login, Logout, DAY ) + 1
                RETURN
                DIVIDE ( Minutes, Days )
            )
        )
    )
VAR Hours =
    QUOTIENT ( AverageTime, 60 )
VAR Minutes =
    FORMAT ( MOD ( AverageTime, 60 ), "00" )
VAR Result = Hours & "hr:" & Minutes & "M"
RETURN
    Result

     

7 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi NB689 
    Please use the following 

    Average Time Logged In = 
VAR TotalTime =
    SUMX ( 
        Data,
        DATEDIFF ( Data[Login Time], Data[Logout Time], MINUTE )
    )
VAR NumberOfUsers = COUNTROWS ( VALUES ( Data[User ID] ) )
VAR AverageTime = DIVIDE ( TotalTime, NumberOfUsers )
VAR Hours = QUOTIENT ( AverageTime, 60 )
VAR Minutes = MOD ( AverageTime, 60 )
VAR Result =
    Hours & "hr:" & Minutes & "M"
RETURN
    Result

     

    • NB689's avatar
      NB689
      Helper I
      4 years ago

      This looks great, but it looks like I failed to include another factor with my source data. It looks like some of the logins can span across multiple days, and I really want to calculate the average time logged in per user per DAY. I have tried to add a variable to calculate the number of days and divide the value by this, but I can't figure out the syntax. Could you show me how to add that in? Really appreciate the help.

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi NB689 

        this is not as simple as you would expect. You have two options: either to expand your table by splitting it over days or to iterate over a Date table. Both requires  a bit complex dax. Please share more realistic sample of data and I look into it tomorrow morning. Good night