Excel pivot table, difference between fields in the same column

microsoft-excel-2013pivot table

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

Best Answer

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.

Related Question