Dec 18 2023 08:50 AM
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...
Dec 19 2023 04:38 AM
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...)?
Dec 19 2023 06:51 AM
@L z. I use Microsoft excel.
Microsoft® Excel® for Microsoft 365 MSO (Version 2310 Build 16.0.16924.20054) 64-bit
Dec 19 2023 07:57 AM
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.
Dec 19 2023 08:00 AM
Dec 19 2023 08:47 AM
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?
Dec 19 2023 11:09 AM - edited Dec 19 2023 11:25 AM
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
Dec 19 2023 12:56 PM
Dec 19 2023 01:00 PM
Dec 19 2023 01:01 PM
Dec 20 2023 01:21 AM
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?
Dec 20 2023 09:21 AM
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 :)
Dec 20 2023 12:00 PM