Limit Data to Top or Bottom Values

You want to quickly focus your analysis on the items of greatest significance to your business question. For example, you want to identify your top 100 customers and what that group of customers is worth.

You can limit the data to the top or bottom values of a set. This helps to keep the data shown in the work area small, even when using large data sources.

You can define a top or bottom rule by specifying

  • a number, such as the top 50 or the bottom 50 sales people by performance
  • a percentage, such as customers who contribute to the top 10% of overall revenue
  • a cumulative sum, such as customers who contribute to the first ten million dollars of overall revenue

To try it yourself, see Example - Find Your Top or Bottom Performers.

If the selected set contains a user-defined filter, the top or bottom rule applies only to the included values. For example, if you apply a filter to show only retailers with revenue greater than one million dollars, the bottom rule applies to the lowest values within those results.

Changing the context filter section of the overview area changes the values shown for the selected set. As a result, the items that meet the top or bottom rule may also change. If you want the filter rules to ignore context changes, you can pin the context.

Procedure

  1. Select the set that you want to limit.
  2. Click the top or bottom button top or bottom icon on the toolbar.

    The Define top or bottom filter dialog box appears.

  3. Under Top or bottom, click Top or Bottom.
  4. Under Number of items, specify a number, percentage, or cumulative sum.
  5. In the By measure box, click the measure that you want to use to specify the top or bottom value.

    Use the default measure when you want the top or bottom rule to be automatically recalculated when the measure changes in the crosstab.

  6. In the For row box or For column box, click the item that contains the values to use to specify the top or bottom rule.

    Use the default item when you want the top or bottom rule to be automatically recalculated during drill and replace operations.

  7. Click OK. To remove the top or bottom filter, under Top or bottom, click None.
Parent topic: Limiting the Items in Your Analysis