Percentage Increase/Decrease in Pivot Table

Question

I have a pivot table with 2 value column and I want to add a column that shows the percentage increase or decrease between the two. I need this to work at any level of the grouping, detail rows, totals and grand totals.

eg

Row field 1 - Country

Row field 2 - City

Data field 1 - Income 2014

Date field 2 - Income 2015

Reguired Data field - (Income 2015-Income 2014)/Income 2014

I've tried doing it in the data table but the summary values in the Pivot don't work correctly. You obviously can't average the increases or sum them to get the group totals.

Can someone assist?

Thanks

Gordon

Answer 1

Hi,

Try this

1. Right click on cell A4 and go to Sort > More Options.  Select Manual
2. Click on More Options and uncheck the tick box there
3. Repeat above steps while on cell A5
4. To sort column E in ascending order, click on cell E4 and click on Z to A under Data.  Click on cell E5 and click on Z to A under Data
5. To sort column G in ascending order, click on cell G4 and click on Z to A under Data.  Click on cell G5 and click on Z to A under Data

Tried and tested in Excel 2013.

Hope this helps.

Answer 2

Hi,

Select any cell in the Value area section of the Pivot Table and then go to Pivot Table Tools > Analyse > Fields, Items & Sets > Calculated Field.

Hope this helps.

Answer 3

This you can use in normal pivot table. Select your data/go to Tab Insert/Pivot Table  and then create a pivot table as per your need.

Now while putting cursor on a cell in pivot table go to tab Analyse/Field,items & Sets/Calcuated Field and as per this screen shot create a calculated field.

The formula used in my calculated filed is 

=(Income2015-Income2014 )/Income2014

Answer 4

Hi Gordon,

You could try adding a calculated field to your pivot table.

If you are using Excel 2010, click the "pivot table options" ribbon and click "Fields, Items & Sets" and click "calculated field"

Name: % Increase 

Formula:  =('Income in 2015' -'Income in 2014' )/'Income in 2014'

and click "add"

Format this column as a percentage

This should work at any level.

Hope this helps!

Vijay

Vijay,

Thanks. I'm using Excel 2013 with Power Pivot and that option is not available.

Gordon

Answer 5

Hi Gordon,

You could try adding a calculated field to your pivot table.

If you are using Excel 2010, click the "pivot table options" ribbon and click "Fields, Items & Sets" and click "calculated field"

Name: % Increase 

Formula:  =('Income in 2015' -'Income in 2014' )/'Income in 2014'

and click "add"

Format this column as a percentage

This should work at any level.

Hope this helps!

Vijay