percentage in pivot table

syamsulamri

New Member
Joined
Oct 2, 2014
Messages
9
Hi,
Need help here.

I have a data

yearprojectplanactualperformance
2011A10.5010.50100%
2011B10.5010.3899%
2012A10.5010.4299%
2012B7.006.8398%
2013A10.509.7993%
2013B10.5010.2598%
2014A10.5010.1797%
2014B10.509.5090%

<tbody>
</tbody>

<tbody>
</tbody>


when converting to pivot it will be like this:

year(All)
Values
Row LabelsSum of planSum of actualSum of performance
A42.0040.88389.29%
B38.5036.96384.52%
Grand Total80.5077.83773.81%

<tbody>
</tbody>

perhaps, i want the pivot will be like this:

year(All)
Values
Row LabelsSum of planSum of actualSum of performance
A42.0040.8897.32%
B38.5036.9697.32%
Grand Total80.5077.8397.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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,591
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top