Excel Pivot calculated field question

[GC102014]

I have table that has the same account names but for different years (sample below). I want to create a pivot item field that calculates the difference between the years for the same account name
Account Name	Year	Amount
Account 1	2014	15000
Account 1	2015	20000
Account 2	2014	30000
Account 2	2015	60000
So for Account 1 the difference is 5K and coount 2 the difference is 30K. How do i get the difference for the two fields when the amounts belong to the same column and can only be differentiated by year?
Sum of Amount	Column Labels
Row Labels	2014	2015	Grand Total
Account 1	15000	20000	35000
Account 2	30000	60000	90000
Grand Total	45000	80000	125000
The output I would like to show is below:
I need the change to be part of the pivot table because I want to then get the top 40 base on change column.
Sum of Amount	Column Labels
Account	2014	2015	Change
Account 2	30000	60000	30000
Account 1	15000	20000	5000
Grand Total	45000	80000	35000
Please help!

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[wynn89]

Hi,

is it possible to pivot your original data into this format

Account Name

2014

2015

<tbody>
</tbody>

Copy and paste this format into a new sheet and we can calculate the change from there by adding a column to the right of "2015"

Regards,
Wynn

 

[GC102014]

I can definitely just add an extra column and just deduct 2015 from the 2015 total but the problem is I need to show top 40 result based on the changed field. Since it is not part of the pivot table it won't sort by the change field

 

[wynn89]

I can definitely just add an extra column and just deduct 2015 from the 2015 total but the problem is I need to show top 40 result based on the changed field. Since it is not part of the pivot table it won't sort by the change field

Hi,

after adding an extra column and getting the change field, format the range into a table and use the "Number Filter" function in the table , click on "Top 10" and filter it to top 40 items

Regards,
Wynn

 

[GC102014]

Hi Wynn,

I've highlighted the pivot table and the "change" column that I've created then did a control L. It did not convert it into table. Any tip on how to do this? So close! Thank You!

 

[wynn89]

Hi GC,

Basically these are the columns you require in a sheet (not pivot table)

Account Name 2014 2015 Change

then change this range into a table and use the number filter

Regards,
Wynn

 

[GC102014]

gotcha. thanks for the tip. it's a good workaround. i just wished though that I can do this via pivot so the users can click on any part pf the pivot and see the details.