Pivot Table Avg Times

[elisprague]

Good morning,

I am attempting to create an excel pivot table that takes avg times per day so that I can plot in a bar chart. I would like to group each time by hour and calculate how many instances where those times appear (per hour). The column/variable of interest is "Arrival Time." I have put the times in the rows (In pivot) but when I attempt to put the times in the values and average them, excel won't calculate the average instances of those times.https://mothership2022-my.sharepoint.com/:x:/g/personal/elsprague_thecounselingcenter_org/EfGNasL9rx... 

[#2]

Hi @elisprague 

#1 How do you calculate the expected average? i.e.:

#2 What version of Excel do you run + on which Operating System (Windows, Mac...)?

[#3]

@L z. I use Microsoft excel.  

Microsoft® Excel® for Microsoft 365 MSO (Version 2310 Build 16.0.16924.20054) 64-bit

[#4]

@L z. 

As you can see, this is how these averages are calculated manually, I want to find a way for pivot table to automatically calculate these averages from the entire data source.  

[#5]

@elisprague 

Do you mean

?

[#6]

Hi Sergei, thank you for responding. I am interested in the occurrences and not the avg times. Our goal is to find out the average number of appointments at specific times a day so that we can plan staff scheduling accordingly.

[#7]

@elisprague 

Creating PivotTable add data to data model

When you drag Arrival Time to rows it automatically create calculating column Arrival Time (Hours). In Power Pivot change formula for that column on

=MROUND([Arrival Time], 1/24)

and apply proper time format.

Next create the measure

Average Arrivals:=DIVIDE(
     COUNTROWS( Admissions_Data),
     CALCULATE( COUNTROWS( Admissions_Data), ALL(Admissions_Data) ) 
 )

and apply proper format to it.

Just number of arrivals will be

Arrivals:=COUNTROWS( Admissions_Data)

As result

I guess blank row in the table is misprint. If you have blanks in actual data formula will be bit more complicated to avoid blanks.

Above is number of appointments and percentage per hour for all periods. Not sure what do you mean under average - average per day, per weekday at certain hour or what?

[#8]

@elisprague 

Have taken a sligthly different route as it seems MROUND does not always put the [Arrival Time] in the appropriate Time Slot:

In attached file the Avg measure is @Sergei Baklan's one

[#9]

Is there a way to get to the average occurrences instead of the percentage? for example, at 12:00 PM we had an average of x amount of appointments.

[#10]

https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/75890
I should also add, that theses averages are based on per day, so per day we would have x amount at y time and so on. I hope that makes sense.

[#11]

Thank you so much Sergei, I am interested in seeing the average per day, so for example, on any given day, you will have on average x amount at y time, so on and so forth.

[#12]

@elisprague 

Sorry, I didn't catch what the average means. At given date you have x amount at y time, like

how averaging of that shall be done?

[#13]

@Sergei Baklan

Thanks Again, I am uninterested in finding percentages just the average number of occurrences for each time slot per time of day (in hour time blocks).  Notice on this example (image below), how the user took all the dates where there was an appointment at 7, 8, 9, 10 (etc)....there was a one added where there was an arrival time occurring in the time increment (column).  Then all the occurrences were divided by the number of dates in the data set.  I would like the pivot table to do this automatically so that I can use a slicer and narrow down month by month cases.  This would allow me to look at the average within the whole data set and to also narrow down to a specific month in the dataset.  I could say, "In January we had about 4 appointments on average take place at 1:00 PM" and so on.  I hope this helps.  Thanks again Sergei :) 

[#14]

Maybe the reason we are not getting the averages is because we are not taking into account unique dates into our averages. Just a thought