Limit Data to Top or Bottom Values

You want to focus your report 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 keeps the amount of 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 50 top or bottom performing sales people
  • 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

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.

You can filter a set of members to show only those at the top or bottom and base the filter on the measure you are using and the set of members on the opposite edge. You can also specify a custom filter.

Procedure

  1. Select a set, click the More icon More icon in the application bar, and click Edit Set.
  2. Click the new button new icon and click Top or Bottom.
  3. Under Type, select whether to filter the Top or Bottom values.
  4. To choose how and how much you want to filter, under Number of items, click Count, Percent, or Sum and type the number of data items to show, the percent of data items to show, or the value to sum.
  5. Choose the intersection on which to base your filter by clicking the ellipsis button and selecting the members and measure for the intersection from either the Source tab Source tab icon or the Calculated members and measures tab Calculated members and measures tab icon.

    For more information, see Create an intersection (tuple).