Excel – calculated column in pivot table from power pivot

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.

In Excel, a Scatter Chart never displays categories on the horizontal axis. If you look closely at Jon Peltier's sample chart, you can see how he worked around this issue: by using numeric values as a proxy for categories. You'll need to do the same thing.

Then you can recreate labels for your X-axis values/categories. A typical method for that is to create an additional series with corresponding x-values and 0 for their associated y-values, then using Rob Bovey's excellent XY Chart Labeler to label that series' data points with your category labels.

Alternatively, you could use a Line Chart type, which will accommodate category values in the X-axis. You'll need to make sure your values line up appropriately in your data source, then format your data point markers on and your lines off.