Forum Discussion
Enabling Time Measure to Average
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 expectedAverage 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
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
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.
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
I might be close on what I want the logic to do by adding in another variable for the number of days, and then adding this in to be divided in the measure.
I do have one issue though. I have added a TotalDays variable into the measure and I made a "Days Duration" measure for troubleshooting to make sure this is calculating correctly. I need the total to show 9 (the actual sum) instead of 5.
Here is my new measure:
Average Time Logged In Per User Per Day = VAR TotalTime = SUMX ( Sheet1, DATEDIFF ( Sheet1[Login], Sheet1[Logout], MINUTE ) ) VAR TotalDays = SUMX ( Sheet1, DATEDIFF ( Sheet1[Login], Sheet1[Logout], Day ) ) +1 VAR NumberOfUsers = COUNTROWS ( VALUES ( Sheet1[Full Name and 3-4] ) ) VAR AverageTime = DIVIDE ( TotalTime, NumberOfUsers ) VAR AverageTimePerDay = DIVIDE ( AverageTime, TotalDays ) VAR Hours = QUOTIENT ( AverageTimePerDay, 60 ) VAR Minutes = MOD ( AverageTimePerDay, 60 ) VAR Result = Hours & "hr:" & Minutes & "M" RETURN ResultHere is what my data currently looks like. The Total for Days Duration should be 9 instead of 5.
I've tried using other commands than SUMX to produce the 9, but haven't been able to figure out how to do that. The DAX for the Days Duration column is the exact same I'm using for the TotalDays variable.
I typed this one the phone so please forgive me if any mistake.
Average Time Logged In Per User Per Day = VAR AverageDailyTime = SUMX ( Sheet1, VAR Minutes = DATEDIFF ( Sheet1[Login], Sheet1[Logout], MINUTE ) VAR Days = DATEDIFF ( Sheet1[Login], Sheet1[Logout], DAY ) + 1 RETURN DIVIDE ( Minutes, Days ) ) VAR NumberOfUsers = COUNTROWS ( VALUES ( Sheet1[Full Name and 3-4] ) ) VAR AverageDailyTimePerUser = DIVIDE ( AverageDailyTime, NumberOfUsers ) VAR Hours = QUOTIENT ( AverageDailyTimePerUser, 60 ) VAR Minutes = FORMAT ( MOD ( AverageDailyTimePerUser, 60 ), "00" ) VAR Result = Hours & "hr:" & Minutes & "M" RETURN Result