Pivot Table Subtotal of Calculated Field Problem

  • Thread starter Thread starter FSUKC
  • Start date Start date
F

FSUKC

I have created a pivot table for the purpose of viewing revenues by
sales person, company and project. I am having a problem with the
subtotals due to my use of a calcuated field. PLEASE HELP!! Here is
the info:

Sample Data

Sales Person Company Project Sale Price
August Order Qty
Sam Company 1 Project 1 $100
10
Sam Company 1 Project 2 $300
5
Sam Company 1 Project 3 $250
10
Sam Company 2 Project 1 $100
20
etc.....

I created a calculated field for the pivot table which is Sale Price *
August Order Qty to get the August Revenue. What I would like it to do
is show Sam and revenue totals per company and project and then
subtotal by company. In the example above, the subtotal for Company 1
is not calculated by adding the revenues for each project for company
one, it's calculated by adding the sales prices for each and the number
of units for each. That is an astronomical number. While I wish my
company's revenue's were that high, they simply aren't accurate and
it's really causing me a problem. HELP!!!!!!!
 
You should calculate the sale price * qty for each row in the source
data. Then, add that field to the pivot table, and the totals will be
accurate.
 
Thank you very much Debra. I see in reviewing other posts that you are
quite a help to many people.

Yes, I have been employing your suggestion for a while and it's been
working just fine. I just thought there had to be a way to avoid
performing the formula outside of the pivot table but it seems that I
cannot. Thanks again!!
 
You're welcome! Pivot table formulas can work well for some
calculations, but aren't too useful for summarizing line totals.
Thank you very much Debra. I see in reviewing other posts that you are
quite a help to many people.

Yes, I have been employing your suggestion for a while and it's been
working just fine. I just thought there had to be a way to avoid
performing the formula outside of the pivot table but it seems that I
cannot. Thanks again!!
Click to expand...
 
Back
Top