Filter the Rank Field in a Pivot Table

{ 4 Comments }

Imagine a two column dataset – Customer Code and Material Number (with alphanumeric data).  The objective is to determine the second highest quantity sold per Customer code.

Since we will first have to determine the Customer wise and Material Number wise quantity sold, a good way to get started is to use a Pivot Table.  One can drag Customer Code and Material Number to the Row labels and Material Number (again) to the Value area section.  We can then sort the numbers in the Value area section in descending order.

Now comes the bit of filtering the Pivot Table to only view the Material Numbers per Customer code which rank second.  To do so, one can try the following steps:

1. Add the Material Number field once again to the Value area section
2. Right click on any cell in the MaterialNumber2 column and choose the “Rank Largest to Smallest” in the “Show Values as” option.  This option is available only in Excel 2010 and higher versions.
3. Click on the Filter drop down of the Row labels heading and select Material Number in the drop down there.  Now go to Value Filters > Equal > Count of Material Number2 > 2

While the steps above sound very logical, the result is incorrect.  The criteria gets applied on the Count column rather than the Rank column.  So the result will be all rows where the Count is 2.

One can overcome this problem by using the PowerPivot.  You may refer to my solution in this workbook.

Leave a Comment Cancel reply

Your email address will not be published.

*

  • Hi Ashish,
    How do i do calculations from 2 separate Pivot tables. For eg. In PIVOT Table eg demand PIVOT which is computed from one sheet, I have

    Skill Level1 Level2 Level3 … Total

    Skill 1 4 8 18 …
    Skill 2
    Skill 3
    Skill 4
    Skill 6
    In another pivot i have data from another sheet call Supply Pivot
    Skill Level1 Level2 Level3

    Skill 1
    Skill 2
    Skill 3
    Skill 4
    Skill 5
    I want to get the difference between the Demand and Supply Pivot. Note that in both pivots, the same skill may not be present. For eg if Skill 5 is in Demand , it may not be in Supply Pivot. In the end, I want one Pivot with the differences of the all the skills between Demand and Supply or at times Supply and demand.