Not so recently, one of our readers and a very avid questioner, Vidhat, asked me the following question
Take a look at this data, we have ECode, Type and Amount
How would you solve that using a Pivot Table?
Please post your interesting solutions in the comments and if you have a file to share, please paste a link to download the file.
I’ll also share 3 solutions (fragile to robust) that I found for this problem
Updated on 31st July 2017
Make a separate calculation outside of the Pivot Table. We do it all the time in case we want to calculate something that cannot be adjusted in the current pivot table calculation
Just in case you get perturbed by GETPIVOTDATA Function that appears when you start writing the formula in the Pivot Table area, click here to turn off GETPIVOTDATA
This is one of the most lame and quick fix solutions that comes to mind in the first place. Let’s take a look at another one which slightly more robust
Use the Pivot Table built in Calculation method : Difference From
This method is cool but you’ll not be able to completely get rid of the extra empty column. Plus if you change the shape of the pivot table this calculation won’t work
Create a measure in PowerPivot and then add it to the pivot table. This method is by far the most robust method of all. Here is how it works
Step 1 : Add the data to the data model while creating a pivot table.
Note that : Data model feature is available starting Excel 2013 but you can manually load the data in PowerPivot window using Excel 2010 as well. Excel 2007 guys.. sorry you need an upgrade!
Step 2: Create a Measure
Once you have created a pivot table
Step 3 : Add the measure “Net Balance” to the pivot table
Note the difference that you don’t even have to drag “TYPE” to the Columns area. PowerPivot measures are robust and automatically adjust to the filter context of the pivot tables