I have pivot table that shows a weekly marketing cost. My source data is broken down by week. However, its been of requested that I show the average DAILY spend in my pivot table.
A quick solution has been to create a calculated field that simply divides the cost by 7.

So like this:
Week Cost Avg. Daily Cost
Week 1 $70 $10
Week 2 $140 $20

Makes sense, right? Except the Grand Total will be wrong.

Is there a way to create a field that will accurately assume each week has a value of 7 days and then accurately create a final grand total average?


My source data looks something like this (headers in bold)

Week Vendor Units Cost
Week 1 NYTimes 100,000 $50,000
Week 1 USA Today 75,000 $30,000
Week 2 NYTimes 150,000 $40,000
Week 2 USA Today 200,000 $60,000
Week 2 Tribune 300,000 $75,000