Excel 2007 Pivot Table Grand Total for Calculated Field

Hi,

I have a pivot table with a calculated field which multiplies two other fields together.  Due to the nature of calculated fields, my Grand Total row is displaying the product of the sums of the items.  I want it to display the sum of the products.  Here's what I'm getting

                   Sum of    Sum of       Sum of                    
Item              Qty          Price          Sales (Calculated Field Qty * Price)
1                     2             1                  2
2                     4             1.5               6
3                     6             3                 18
Grand Total    12           5.5              66

I want the Sum of Sales column's Grand Total to be 26.

Thanks!

Jason
JasonScarbroughAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeBlackmanCommented:
Hi,

Could you possibly copy the sheet into a new workbook and post it so I can have a look?
0
JasonScarbroughAuthor Commented:
I saved this as .xls 97-2003 from 2007 and got compatibility warnings, so I hope it works.  It's pretty basic though.  I just threw it together to demonstrate the problem.
Book3.xls
0
Jerry PaladinoCommented:
Jason,
Calculated fields in a Pivot Table are evaluated at the aggregate level, not the record or item level.  Please read Zorvek's comments in this EE Question that I believe will help you understand how the calculated fields work with Pivot Tables.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24367410.html
In the attached file I added a field to your data area and then added the new field to the pivot table.   This provides the results you are looking for.
HTH,
Jerry

Book3-1-.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JasonScarbroughAuthor Commented:
Thank you ProdOps.  I thought adding another field to the source data is what I would have to do, but I thought I'd check with the experts to see if there was another solution by which I wouldn't.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.