Suppose you want to find the top retailers that contribute 20 million in sales.
How to Filter top / bottom items that create a specified value in Pivot Table
You can do this by using the top 10 filters in the pivot table.
- Go to Row Label filter – Value Filters – Top 10.
- In the Top 10 Filters dialog box, there are four options that you need to specify:
- Top / Bottom: In this case since we are looking for the top retailers that make 20 million in total sales, select the top.
- In the second area, you need to specify a price that should be for the top retailers. In this case, since we want to get top retailers who make up 20 million in sales, it would be 20000000.
- Now select Sum.
- The last field lists all the various values listed in the Price field. In this case, since we only have sales totals, it shows only ‘sales totals’.
This will appear – filtered list of top retailers that make up 20 million of total sales.