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

Excel 2010 with free PowerPivot Add-In.

Compatible with Office 2013 Pro Plus.

Ditto Ashish.

With my own fake data.

http://www.mediafire.com/view/8pmdd8ic44ajpc0/04_09_15a.xlsx

2013 file on request.

Herbert,

I'm running Excel 2013 and Powerpivot. I've got my report to do what I want by modifying the data source as follows.

Old version - one record per client

Code, Group Name, Client Name bills2013, bills2014, bills2015

New version - one record for each year for each client.

Code, Group Name, Client Name, year, bills

I understand this is a better way to structure it. I can now add the "year" field to Row, add the bills twice to show the value and the % change from the previous year.

My outstanding issue is that I want to sort the report by the % column to show the codes with the largest increase at the top of the report. I've tried sorting in various ways, but can't get it to sort on the % column, only the value column.

In this snippet, the top two rows should be in the opposite order as I told it to sort by "% Change" not Billings.

It looks like it sorts on the underlying value, not the calculate percentage that is displayed. How do I sort on what it shown?

Thanks

Gordon

Sanitised version uploaded at SanitisedANZSIC

Answer 3

Excel 2010 with free PowerPivot Add-In.

Compatible with Office 2013 Pro Plus.

Ditto Ashish.

With my own fake data.

http://www.mediafire.com/view/8pmdd8ic44ajpc0/04_09_15a.xlsx

2013 file on request.

Answer 4

Hi,

Since the last 3 options in your screenshot are highlighted, it means that the Pivot Table has been created from the PowerPivot tool.

Upload the workbook to OneDrive and share the link of the workbook here.

Answer 5

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.

Ashish,

That option is not available to me.

Thanks

Gordon