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.
| User Picking | PickID | Average Pick Time Orderline |
| USER1 | 00001 | 00:00:35 |
| USER1 | 00002 | 00:01:42 |
| USER1 | 00003 | 00:00:43 |
| USER2 | 00004 | 00:05:27 |
| USER2 | 00005 | 00:00:37 |
| USER3 | 00006 | 00:03:13 |
| USER3 | 00007 | 00:00:33 |
| USER4 | 00008 | 00:00:57 |
| USER4 | 00009 | 00:01:01 |
I know that the average times per user in order are 00:01:00, 00:03:02, 00:01:48, and 00:00:59. I want to add a fourth column with these values per user.
I know how I could do that in Excel, which is where this data is being taken from, but I'd like to learn how to do it in Power BI. Thank you!
Edit: I created a measure to trying to do this based off a similar thread like mine but it didn't work.
Measure = AVERAGEX(SUMMARIZE(Table1, Table1[USER_PICKING], Table1[AVERAGE_PICK_TIME_ORDERLINE]), Table1[AVERAGE_PICK_TIME_ORDERLINE])
The error I got was
"Couldn't load the data for this visual
MdxScript(Model) (4, 117) Calculation error in measure 'Table1'[Measure]: The function AVERAGEX cannot work with value of type String."
- 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])
6 Replies
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])
Power Bi works very similar to excel, if in excel your cell format is Text you will not be able to make calculations of any type with values
Before making the formulas make sure that your times are in "Time, Date, or Datetime"
Otherwise convert the column type or pass the function to convert them before calculating with the data
