inforr
asked on
Pivot Table Calculations
Hi,
I have an XLS attached where I want to perform some additional calculations in the pivot table. The Data tab shows the data which is a 6 week history of tickets/calls over this period.
The Pivot Table sheet shows the pivot table I have created showing the number of tickets/calls received per hour for each day and week.
The calculations I want to perform are around averages. I want to add the following:
- Show average number of tickets per week by hour of the day with total average for week (ideally a summary row at the bottom of the pivot table)
- Show average number of tickets per same weekday (Mondays, Tuesdays etc) by hour of the day with total average for whole day (again a summary at the bottom)
An example is show in yellow at the bottom of the pivot table.
Can these calculations be shown in the same pivot table or do they need to be in a separate one?
Thanks.
Pivot-Table-Calc.xlsx
I have an XLS attached where I want to perform some additional calculations in the pivot table. The Data tab shows the data which is a 6 week history of tickets/calls over this period.
The Pivot Table sheet shows the pivot table I have created showing the number of tickets/calls received per hour for each day and week.
The calculations I want to perform are around averages. I want to add the following:
- Show average number of tickets per week by hour of the day with total average for week (ideally a summary row at the bottom of the pivot table)
- Show average number of tickets per same weekday (Mondays, Tuesdays etc) by hour of the day with total average for whole day (again a summary at the bottom)
An example is show in yellow at the bottom of the pivot table.
Can these calculations be shown in the same pivot table or do they need to be in a separate one?
Thanks.
Pivot-Table-Calc.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The table in yellow was your table at the bottom of the sheet in your original question - there for an example for me and I didn't delete it.
I don't believe this can be done in a calculated field the way everything is set up and while you can do the actual calculated ITEM for what you want, you can't get it formatted the way you want at the same time, lol.
Recall me comment >> You can click on the Day field and insert the wk Avg calculated field (re: (Mon+Tue+Wed+Thu+Fri+Sat)/
Dave
I don't believe this can be done in a calculated field the way everything is set up and while you can do the actual calculated ITEM for what you want, you can't get it formatted the way you want at the same time, lol.
Recall me comment >> You can click on the Day field and insert the wk Avg calculated field (re: (Mon+Tue+Wed+Thu+Fri+Sat)/
Dave
ASKER
Thanks for your response.
The COUNTIFS function is useful in this case as it gives me the totals as you describe from which I can derive the averages. I have a few follow up questions:
- It is possible to build this into the pivot table using a calculated field?
- What is the purpose of the table in yellow?
Thanks,
Chris.