I am trying to produce a set of 8 pivot charts; one for each day of the week, and one with the full week. Please see the attached workbook for my current status.
The horizontal axis should be Column B (a unique time stamp), and the vertical axis should show the running totals of Columns J (labelled "M1") and Column K (labelled "M2").
Sheet4 is filtered to display 15Oct18 only and has a problem I can't seem to solve.
1. I cannot get the pivot table/chart to accumulate past the individual hours mark, and show the cumulative total for the whole day. I've tried everything I can think of, but each inspiration just screws up the pivot table.
Can you offer any suggestions?
Thanks!
Hi Bob,
Unfortunately that's how PivotTables work. The workaround is to create a helper column that groups the dates and times into the increments you want. However, it won't have the nice nested axis labels you'd like, because once you add another row label to the PivotTable the running total gets interrupted.
See example attached.
If you can use Power Pivot then you could write a custom measure that can get around this limitation.
Mynda
