Need Help with a Pivot Table Grand Totals

[pbcox]

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!

[stasinek]

I suppose you are looking for a weighted average cost. You can do it using PowerPivot by adding a measure (for each row the number of hours * cost per hour divided by the total number of hours).

[pbcox]

Thanks for your reply but that is not quite what I am looking for. In my pivot table, cells C4, C5 & C6 are correct as shown. These are average hours for each department. But then in cell C7 I am wanting the sum of those 3 averages (252,680.) And then also cells D4, D5 & D6 are correct as shown, but in cell D7 I am looking for an average an amount equal to cell B7 / C7 (1.87.)

Thanks!

[rorya]

Assuming you have Power Pivot since you're on 365, you should be able to get the averages to sum with a measure. Similar formulas to the ones in this thread but using AVERAGE rather than MAX. The last column can then just be calculated by dividing the other two measures, rather than averaging the cost per hour field.

Edited to add example - you will need to upgrade the model.

[pbcox]

Assuming you have Power Pivot since you're on 365, you should be able to get the averages to sum with a measure. Similar formulas to the ones in -LINK DELETED - but using AVERAGE rather than MAX. The last column can then just be calculated by dividing the other two measures, rather than averaging the cost per hour field.

Edited to add example - you will need to upgrade the model.

Thanks for your reply. Attached is an updated example using Power Pivot functionality with a data model. I have the hours part working but now I can't figure out how to get Cost per hour. You don't seem to be able to add fields to Power Pivot tables in the same way you can to regular pivot tables.

Thanks

[rorya]

I can't open that with 2010 unfortunately. You need to add a measure that simply divides the other two measures (as in the sample I posted). I assume you added a measure for the hours?

[pbcox]

I can't open that with 2010 unfortunately. You need to add a measure that simply divides the other two measures (as in the sample I posted). I assume you added a measure for the hours?

Thanks. I finally figured out how to enter the formula. The interface is less user friendly in Power Pivot vs Regular Pivot tables. Thanks for your help.

[rorya]

With great power comes great complexity, unfortunately!

Glad to help.