Share [Social9_Share]

Pivot Table Problem

Difference Between 2 values in Pivot 3

Not so recently, one of our readers and a very avid questioner, Vidhat, asked me the following question

Difference Between 2 columns in Pivot 1

Let me explain with a little more detail!

Take a look at this data, we have ECode, Type and Amount

Difference Between 2 columns in Pivot 2

  1. An Employee Code can appear multiple times in this table
  2. The employee can ask for a reimbursement amount or the company can charge an amount from the employee
  3. The Problem – You want to calculate the net balance (+ve or -ve) i.e. Charged – Reimburse for each employee

 

How would you solve that using a Pivot Table?

DOWNLOAD THE EXCEL FILE HERE

 

Post your answers in the comments

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

Solution #1

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

Difference Between 2 values in Pivot 4

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

 

Solution 2

Use the Pivot Table built in Calculation method : Difference From

Difference Between 2 values in Pivot 5

  1. Right Click on the Charged Column
  2. Go to Show Values as and select “Difference From”
  3. Base Field = “TYPE” and Base Item = “Charged”
  4. And you’ll get the difference between the two values

 

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

 

Solution 3

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.

Difference Between 2 values in Pivot 6

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

Difference Between 2 values in Pivot 7

Difference Between 2 values in Pivot 8

Once you have created a pivot table

  • Right click on the Records Table in the pivot field list and choose “Add Measure”
  • In the measure box – Give your measure a name
  • Write the DAX Formula as shown
  • OK

 

Step 3 : Add the measure “Net Balance” to the pivot table

Difference Between 2 values in Pivot 9

DOWNLOAD THE SOLUTION FILE

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

 

  • GraH

    Make a simple pivot is which TYPE is a column header, EMPCODE a row header and AMOUNT is SUM value. Go to Field Settings then Show Value as and choose: Difference from, base field is TYPE and Base Item is Reimburse.
    Should look like this:
    https://uploads.disquscdn.com/images/ffea24236f1330da2dc60835cda87dd74654c1fa48e70e416f3106df66398bc7.png

    My first idea however was to make a calculated field with an IF statement to return a negative or positive number depending of the type. But that failed to my surprise.

    Second alternative is inserting the same IF statement in an extra column in the raw data. Then using this Corrected Amount in the Pivot Table.

    • Both methods are bang on! Calculated Fields won’t be possible in a simple Pivot Table and that is where PowerPivot can be used!

      • GraH

        Yup, PowerPivot is like limitless. Either with a calculated column, witch is like adding the if-statement in the raw data, or as a measure using SUMX to iterate over each row and FILTER. I’m guessing CALCULATE also does the trick.

        3 measures:
        – Charged:=sumx(FILTER(Table1;Table1[TYPE]=”Charged”);Table1[Amount])
        – Reim:=sumx(FILTER(Table1;Table1[TYPE]=”Reimburse”);Table1[Amount])
        – Delta:=[Charged]-[Reim]

        I’ve added all 3 of the them in the Pivot, but you can only use the 3rd measure to show the difference. And I should have named my table1 into something more meaningful.
        https://uploads.disquscdn.com/images/37b3ce68eed0b0550a619a832aa8e9d3e703f21c5d35232ec590ca7542de16a3.png

        • Since there is no need of iterating each row you can do that with a simple Calculate function

          • GraH

            That’s why I added the calculate-solution, later. The concept of filter context is sometimes playing tricks with my head :-).

  • GraH

    With calculate in PowerPivot:
    Delta2:=CALCULATE ( sum ( Table1[Amount]) ; Table1[TYPE] = “Charged” ) – CALCULATE ( sum ( Table1[Amount]) ; Table1[TYPE] = “Reimburse” )

Chandeep Chhabra

Popular Posts

© Goodly