Excel pivot table, difference between fields in the same column

Question

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

Solution

The closest pivot table I can get is as follows:

ROWS: Account Name
COLS: Year
VALUES: Amount

Then right-click on one of the values and choose "Show values as". Change that from the default "No calculation" to "Difference From" with a Base Field of "Year" and Base Item of (previous)

Now you are close but if you also want to see the original data, you need to add amount back into the VALUES a second time. Tweak the headings and you are done.

UPDATE: An alternative approach would be to add another column to the source data with the following formula (assumes you've formatted the source data as an Excel Table and sorted by Account/Year):

=IF([@Account]=R[-1]C1, IF(ISNUMBER(R[-1]C), [@Amount]-R[-1]C3, 0), 0)

That only works if you don't have any missing years in your data. However, you could then apply a top-10 filter to that column which would change the pivot table display accordingly.

Comments

[GC10]

Thanks I've used the "difference from" in the values & did put amount in the values the second time. Values now have change amount, 2014 & 2015 amount.The second problem that I'm having is that (1) "Change" field has is present twice in the values because there are 2 years in columns (2014, 2015). I've tried to hide the change value with no content but the column with change content is getting an error instead of the difference from the 2 years. Second (major), the top 10 values based on "change" amount does not work. Any clue why top 10 does not work?

[Julian Knight]

You can hide the first year Diff by clicking the column heading, right click and hide column. Not perfect but OK. Top-10 seems to work for me. Click in the blank cell ABOVE the heading for the Diff column you want to filter on, right click/filter/top-10

[Julian Knight]

I take back the top-10 comment. It filtered top-10 years not differences! I think the only way to do that is to sort your source data by name/year and then create a new column to calculate the diff. That might be a complex formula if you have missing years for accounts (sparse data).