Calculate an average time based on different users

[Anonymous]

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."

[Manner_Valdez]

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

[amitchandak]

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

[Anonymous]

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

[fhill · Accepted solution]

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

[Anonymous]

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!

[fhill]

Yes, I diffently had to do some Data Formatting to make things look right, even though some things were Date/Times in reality.

FOrrest