# Pivot table formula for percentage?

#### samst

##### Board Regular
Hi

In my Pivot Table I pull data from 2 columns of a spreadsheet (Total Cost and Total Price) What I'm trying to do is have the Pivot table reflect the percentage difference but I keep getting #N/A as a result.
The spreadsheet also has a column for the percentage difference but when I include this column in the Pivot table it sums the percentages rather than giving me the acurate percentage for the grand total. I tried adding the Total price field twice and then making the second a "% of" with the Total Cost field as the Base but that just returns the #N/A eror. Anyone have any thoughts?

Thanks as always!!

#### Mark W.

##### MrExcel MVP
We would have an easier time assisting you if you provided some sample data, and precisely identified the needed calculation.

#### samst

##### Board Regular
Thanks for the reply. I'll simplify the example here, but basically

A B C D E
1 NAME Status Total Cost Total Price % Difference
2 X Complete \$5 \$10 50% (B2-A2)/B2)
3 Y In Progress \$25 \$30 16%
etc...

My Pivot Table looks like this:

Project Status Total Cost Total Price %Diff
X Complete 5 10 50
Y In Progress 25 30 16

GRAND TOTAL 30 40 66%

I can't figure out how to make the Grand Total % accurate. I don't want it to add up the percentages I want it to give me the percent of the Grand total difference between cost and price.
Thanks again!!

#### Mark W.

##### MrExcel MVP
Use a Calculated Field defined as...

=('Total Price'-'Total Cost')/'Total Price'

...and formatted as 0% to get...
Book1
CDEFGHIJKL
1TotalCostTotalPrice%DifferenceData
2\$5\$1050%NAMEStatusSumofTotalCostSumofTotalPrice%Difference
3\$25\$3017%XComplete51050%
4YIn_Progress253017%
5GrandTotal304025%
6
7
8
Sheet3

#### samst

##### Board Regular
Thanks!!
Sorry, but Can you tell me how I do that? or where I put it? I understand the formula I jest don't know where to put it in the pivot table

#### Mark W.

##### MrExcel MVP
With the 'Data' PivotTable button selected choose the Insert | Calculated Field... menu command.

#### samst

##### Board Regular
Thanks!!! I got it with your help!!

#### ilsem

##### New Member
HI

I have excel 2007. Please show how you would work out the % in the same way, as I tried it, and on the individual cell the data is correct but on the grand total line it shows it as zero and does not apply the formula.

I used the formula

=SUM('MAY 2009 H&B'/'MAY 2008 H&B'*100-100)

=sum(33,296,271 / 46,852,649 *100-100)

Result should be -29, but it shows zero