Percent (field1/ field2) on pivot table

[David Chang]

Hi, I have the following pivotal table

year region1 region 2
2001 customer_cnt sales_total customer_cnt
sales_total
2002 customer_cnt sales_total customer_cnt
sales_total
2003 customer_cnt sales_total customer_cnt
sales_total
...
2006 customer_cnt sales_total customer_cnt
sales_total


I would like to create the sales per customer field
(sales_total/customer_cnt) for each year and region, but do not know how to
do it.

Your kind answers will be greatly appreciated.

Jay

p.s.
I believe this question may have been answered in the past. But, I could not
find the solutions easily on line.... Sorry.

 

[Guest]

Can you go into formulas on the pivot chart toolbar, then enter the formula
in there (double clicking on the fields you want). You need to use the
toolbar, just right clicking doesn't give you the formula option.
Regards, Light

 

[Herbert Seidenberg]

Here are some more details.
Assume your base data had these headers and ranges:
Year (2000-2005) Reg (Reg1, Reg2)
Type (CCount,STotal) Value (any number)
Then your Pivot Table might looks like this:

Sum of Value Reg Type
Reg1 Reg2
Year CCount STotal ST/CC CCount STotal ST/CC
2000 72 526 7.31 132 904 6.85
2001 84 1285 15.30 49 215 4.39
2002 194 3466 17.87 76 758 9.97
2003 241 3357 13.93 31 988 31.87
2004 46 1458 31.70 67 386 5.76
2005 350 1786 5.10 180 660 3.67

ST/CC are the added fields, so ignore them for the moment.
Click on the cell that says CCount or STotal (not Reg1 or Reg2).

From the Pivot Table tool bar, select

Formula > Calculated Item (not Calculated Field)
In the Fields box on the left, select Type.

From the Items box on the right, select STotal and CCount

so the formula will read:
=STotal/CCount
Change the name from Formula1 to ST/CC