I have two sets of data that I am trying to combine into a single pivot table and I am having one challenge with the Grand Totals...
1. First data set is PO transactions that are associated with different department #s.
2. Second data set is operating hours for each department
My objective is to use a Pivot table to get to cost per operating hour by department and then a cost per operating hour for all departments. This is a very simplified example as I have other variables I am omitting but if I can solve this I can solve the rest. I know I could use formulas but I like the data selection flexibility of using a pivot table. for example I could include periods and I can roll up the departments to managers and have easy data selection with the pivot table.
In the file I am attaching, what I have done is start with the cost data and then for each record, I have simply used a vlookup to add the hours for each department and then I added a 2nd field where I calculated cost per hour.
In my pivot table, I summed the cost, I averaged the department hours and then I summed the cost per hour. This works for the individual records in the table but the "Grand Totals" don't work. I need to Sum the Average Dept hours above and then I need to a cost per hour for those totals.
So any creative suggestions will be appreciated. Let me know if you have questions.
Thanks!
Bookmarks