Microsoft Office Tutorials and References
In Depth Information
Chapter 9: Components That Group Data
The Top 10 Filter (Sales Rep) dialog box appears (see Figure 9-5).
Figure 9-5: Specify the filter you want to apply.
3. In the Top 10 Filter (Sales Rep) dialog box, define the view you’re looking for. In this example,
you want the Top 10 Items (Sales Reps) as defined by the Sales_Amount field.
4. Click OK to apply the filter.
At this point, your pivot table is filtered to show you the top ten sales reps for the selected
Region and Market. You can change the Market filter to Charlotte and get the top ten sales
reps for Charlotte only (see Figure 9-6).
5. To view the bottom ten Sales Rep list, copy the entire pivot table and paste it next to the
6. Repeat Steps 2–4 in the newly copied pivot table, except this time choose to filter on the
bottom ten items as defined by the Sales_Amount field.
If all went well, you now have two pivot tables similar to Figure 9-7: one that shows the top ten sales
reps and one that shows the bottom ten. You can link back to these two pivot tables in the analysis
layer of your data model using formulas. This way, when you update the data, your top and bottom
values display the new information.
Figure 9-6: You can interactively filter your pivot table report to instantly show the top ten sales reps
for any Region and Market.