Skip to main content

Year Over Year calculation using Pivot Table



Instead of creating a formula outside of the pivot table, you can do this inside the pivot table.

Start from the image with column D empty. Drag Revenue a second time to the Values area.

Look in the Columns section of the Pivot Table Fields panel. You will see a tile called Values that appears below Date. Drag that tile so it is below the Date field. Your pivot table should look like this:

This pivot table has customers down the left side. Across the top are four columns: Sum of Revenue for 2021, 2022. Then Sum of Revenue2 for 2021 and 2022.

Double-click the Sum of Revenue2 heading in D4 to display the Value Field Settings dialog. Click on the tab for Show Values As.

Change the drop-down menu to % Difference From. Change the Base Field to Date. Change the Base Item to (Previous Item). Type a better name than Sum of Revenue2 - perhaps % Change. Click OK.

In the Value Field Settings dialog, choose the second tab, called Show Values As. In the top drop-down menu, choose % Difference From. The Base Field should be Date. The Base Item should be (previous).

You will have a mostly blank column D (because the pivot table can't calculate a percentage change for the first year. Right-click the D and choose Hide.

Labels:

Comments

Post a Comment

Popular posts from this blog

Power Point - Slide Master a very important tool

  When you want all your slides to contain the same fonts and images (such as logos), you can make those changes in one place—the Slide Master, and they'll be applied to all your slides. To open Slide Master view, on the   View   tab, select   Slide Master : The master slide is the top slide in the thumbnail pane on the left side of the window. The related layout masters appear just below the slide master (as in this picture from PowerPoint for macOS): 1  Slide master 2  Layout masters When you edit the slide master, all slides that are based on that master will contain those changes. However, the majority of changes that you make will most likely be to the layout masters related to the master. When you make changes to layout masters and the slide master in Slide Master view, other people working in your presentation (in Normal view) can’t accidentally delete or edit what you’ve done. Conversely, if you're working in Normal view and find that you'...
Post a Comment
Read more