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])
Make sure it is detected as time and then refer
:
https://community.powerbi.com/t5/Desktop/Need-to-calculate-average-of-time-column/td-p/224112
So I was able to convert the AVERAGE_PICK_TIME_ORDERLINE times to seconds, but when I enter the measure in my OP the new column is just a repeat of the previous column.
I essentially want the fourth column
User Picking PickID Average Pick Time Orderline Average Pick Time User USER1 00001 00:00:35 00:01:00 USER1 00002 00:01:42 00:01:00 USER1 00003 00:00:43 00:01:00 USER2 00004 00:05:27 00:03:02 USER2 00005 00:00:37 00:03:02 USER3 00006 00:03:13 00:01:48 USER3 00007 00:00:33 00:01:48 USER4 00008 00:00:57 00:00:59 USER4 00009 00:01:01 00:00:59 - 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])
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!