Forum Discussion
Calculate an average time based on different users
Hello, So I'm fairly new to Power BI and I'm still trying to learn the ropes. What I'm trying to do is basically calculate a user's average time overall. I'll make a a mock table as an example. ...
- Create 3 new COLUMNS (not Measures) - Or you can try to Push these all together using VARS, but I like to keep it simple. You'll need to do Modeling-> Formatting to get things back into Date/Time HH:MM:SS formats, but this is the general idea... And make sure everything is set to 'Don't Summarize'.FOrrestTotal Seconds = (HOUR(Table1[Pick Time]) * 3600) + (MINUTE(Table1[Pick Time]) * 60) + (SECOND(Table1[Pick Time]))Average Column = CALCULATE(AVERAGE(Table1[Total Seconds]), ALLEXCEPT(Table1, Table1[User Picking]))Average Time = TIME(0,0,Table1[Average Column])
Create 3 new COLUMNS (not Measures) - Or you can try to Push these all together using VARS, but I like to keep it simple. You'll need to do Modeling-> Formatting to get things back into Date/Time HH:MM:SS formats, but this is the general idea... And make sure everything is set to 'Don't Summarize'.
FOrrest
Total Seconds = (HOUR(Table1[Pick Time]) * 3600) + (MINUTE(Table1[Pick Time]) * 60) + (SECOND(Table1[Pick Time]))
Average Column = CALCULATE(AVERAGE(Table1[Total Seconds]), ALLEXCEPT(Table1, Table1[User Picking]))
Average Time = TIME(0,0,Table1[Average Column])
Thank you!
The only issue I ran into afterwrads was that it took the value as a time and not a duration (12:01:30 A.M vs 1 minute 30 seconds)
But the math works out!
Yes, I diffently had to do some Data Formatting to make things look right, even though some things were Date/Times in reality.
FOrrest