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])
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'.
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