Pct Difference From – Secrets Revealed

Please read the earlier article about Pct Difference From in order to learn the background. This article shows how to take this one step further.

Pct Difference From

Here is the base pivot table showing Products in row area and Months in column area.

Base Pivot

We can set a particular product as a benchmark and see the difference from that product. Notice that the cursor is in a cell referring to After Mint and March.

Right click and choose Show Values As – (%) Pct  Difference From. Now this dialog appears.

Pct Difference From - Base dialog

Notice that there are two dropdowns. Let us go with the defaults. It is based upon the row area value – After Mint. This is the output – showing how other products sold compared to After Mint. You may see lot of #NULL values. Eliminate them by going to Pivot Table Options – first tab – For Error Values Show: – activate it but do not specify any character.


Pct Difference from - Comparison

After Mint row is empty because it is the basis. All other values in each column are comparison to the After Mint value in that column.

The Dropdown

Now what if I want to use Pct Difference from with columns – months. If I apply that in the current position, March will become the base month, all values in March will be empty and then it will show the difference for each month from March.

But usually we do not fix a particular month while viewing monthly data. We want to compare each month with previous month. How do you do this? The answer lies in the same Pct Difference From Dropdowns. From the Base Field – choose Months and from Base Item choose PREVIOUS. Did you expect that one?

Pct Difference From - Dialog

Pct Difference From - MOM growth

Now we get MOM growth by product. Brilliant, is it not? Try it out with your data and send a much more interesting and useful report to your boss next time.

Queries | Comments | Suggestions | Wish list