percentage in pivot table

[syamsulamri]

Hi,
Need help here.

I have a data

year project plan actual performance 2011 A 10.50 10.50 100% 2011 B 10.50 10.38 99% 2012 A 10.50 10.42 99% 2012 B 7.00 6.83 98% 2013 A 10.50 9.79 93% 2013 B 10.50 10.25 98% 2014 A 10.50 10.17 97% 2014 B 10.50 9.50 90% <tbody> </tbody>

<tbody>
</tbody>

when converting to pivot it will be like this:

year	(All)
	Values
Row Labels	Sum of plan	Sum of actual	Sum of performance
A	42.00	40.88	389.29%
B	38.50	36.96	384.52%
Grand Total	80.50	77.83	773.81%

<tbody>
</tbody>

perhaps, i want the pivot will be like this:

year	(All)
	Values
Row Labels	Sum of plan	Sum of actual	Sum of performance
A	42.00	40.88	97.32%
B	38.50	36.96	97.32%
Grand Total	80.50	77.83	97.32%

<tbody>
</tbody>

It looks like sum of performance is total of percentage but i'm looking of
performance=sum of actual/sum of plan.


Appreciate if somebody can help me on this issue.

Thanks

 

[Marcelo Branco]

Use only four columns (do not use performance) as raw data to build the Pivot Table, i.e.,
year project plan actual

Then insert a calculated field (see http://www.contextures.com/excel-pivot-table-calculated-field.html )
Name: performance
Formula: actual/plan

Format as percent

You get something like this

	G​	H​	I​	J​
3​	year​	(All)​
4​
5​	Row Labels​	Sum of plan​	Sum of actual​	Sum of Performance​
6​	A​	42,00​	40,88​	97,33%​
7​	B​	38,50​	36,96​	96,00%​
8​	Grand Total​	80,50​	77,84​	96,70%​

Hope this helps

M.

 

[syamsulamri]

Thanks Marcelo branco.. thats help