Excel - Pivot table help please

[Royal Fleet]

I have tried Googling for this but without being able to nail down exactly what I am trying to do here.

I have created a Pivot table from a dataset. It has underneath each geographic region 2 columns for a year, 2018 and 2019.

At present within each row therefore it has a 2018 column value and 2019 column value for each line of business (the rows). Excel as a default then includes a subtotal

So something like this

______________2018___2019___Sub Total
LOB A__________25_____50______75\
(Etc)

What I want to be able to do is to change the Subtotal into a % change so in the example above the Subotoal of 75 would change to 100% increase (being (50-25)/25 - expressed as a percentage).

Is there a way to do this without just copying and pasting the values out and doing manually. I have many filters to repeat this with so that will be laborious.

Any help appreciated

 

[Martynt74]

You’d do it as a calculated field.

In the Analyse bit at the top. Towards the right is Fields, Items and Sets and within that is Calculated Field

Click that and then create your formula for the % difference ((2018-2019)2018). It’ll allow you to add it as it’s own column

(Hope that explains it. I’m currently posting from the throne!)

 

[Meeko]

I don't think Excel will allow you to do exactly what you are looking for, but a potential solution would be :

Drag your Values into the pivot table a second time and then go to 'Value Field Settings' > 'Show Values As' tab > Select '% Difference From' in the drop down, select your 'Year' as the 'Base Field' and '(previous)' as 'Base Item'.

This will give you a secondary column showing the %age change vs the previous year. You could then move the 'Values' from Column to Row and you would get the following layout...



______________2018___2019___Sub Total
LOB A
Value_________58_____99_____157
% Change____________70%______
LOB B
Value_________64_____52_____116
% Change____ _______-18%______



EDIT : Or what Marvt suggested. Calculated field may work depending on how your data is structured.

 

[Martynt74]

To add, my solution needs 2018 and 2019 data to be in separate columns in your source table rather than rows

Created a really simple example you might be able to play with to help understand it.

https://ufile.io/nzuhe

 

[Royal Fleet]

To add, my solution needs 2018 and 2019 data to be in separate columns in your source table rather than rows

That is probably why I couldn’t get the solution from Googling. My data is all in rows with year as a single column. Not a lot I can do about that due to the nature of the presented data.

Thanks for your help. I’ll have a look at Meeko’s solution.

 

[Martynt74]

Ah, yeah @Meeko's is the best option then.

I'm not sure i'd drag it to the row though. If you drag the Sales in a second time and set it to % different from previous you'll then just end up with Sales 2 blank for 2018. You can then just hide this column

 

[Syla5]

Create your Pivot table.

Next you need to add a calculated field, this is found in the "ANALYZE" tab -> "Fields,Items, & Sets" -> Calculated Field

You then name your field and in the formula you complete your sum, in this case I just did = '2018' / '2019' then formatted the field to show as a %.

Resulted in

Row Labels Sum of 2018 Sum of 2019 Sum of Average
LOB A ________ 25 _______ 50 _______ 50%
LOB B ________ 75 _______ 25 _______300%