Pivot table formula for percentage?

[samst]

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.]

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

 

[samst]

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

My spreadsheet looks like this
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.]

Use a Calculated Field defined as...

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

...and formatted as 0% to get...

Book1
	C	D	E	F	G	H	I	J	K	L
1	TotalCost	TotalPrice	%Difference				Data
2	$5	$10	50%		NAME	Status	SumofTotalCost	SumofTotalPrice	%Difference
3	$25	$30	17%		X	Complete	5	10	50%
4					Y	In_Progress	25	30	17%
5					GrandTotal		30	40	25%
6
7
8
Sheet3

 

[samst]

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.]

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

 

[samst]

Thanks!!! I got it with your help!!

 

[ilsem]

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

Please help