Displaying top/bottom records

Contents[Hide]

1. Overview

You can display the Top and/or Bottom records using the Top/Bottom Measure property in the Metric Set Binding. This can be done either at the Metric Set level (i.e. using the Metric Set designer), or at the dashboard level. If you have filters, the data gets filtered first, and then the records are displayed. 

This example configures a the metric set to return only 5 rows. These rows include the top 4 records and another record for Other, which groups and aggregates all the smaller records.

2. Prepare the data

From the Adventure Works database, expand the [Sales].[SalesTerritory] table. Create a new Table data visualization using SalesYTD as a measure and TerritoryID as the rows. Drag the Name hierarchy overtop TerritoryID to display the relevant name:

Sample Data
Sample Data

3. Set the Top / Bottom measure

  1. Right-click the table, and select Data Analysis Panel.

    Open the Data Analysis Panel
    Open the Data Analysis Panel

  2. Click the Edit icon.

    Click Edit
    Click Edit

  3. In the Metric Set Default Values section, set the following values:
    • Top / Bottom Measure: SalesYTD
    • Top: 5
    • Bottom: 0

    Set the values
    Set the values

    Note
    Records with the same value will be "tied," which may result in more than the requested total number of records. From the Tie Policy drop-down, select Exclude Ties to exclude tied records if their number goes beyond the requested total.

Result:

Result showing Top 5 values
Result showing Top 5 values

4. Set the Other Group measure

  1. Open the Metric Set Binding Configuration panel again and click Edit.
  2. In the Metric Set Default Values section, set the following values:
    • Other Group Measure: SalesYTD
    • Hierarchy: Name
    • Category: Bottom
    • Upper Boundary Category: Positional

      Note
      This option is a dynamic value that allows a specific number of points to be displayed. Other options are Fixed, which compares against a specific value, and Percentage.

    • Upper Boundary: 4

    Set the Other group values
    Set the Other group values

Result:

Result showing Top 5 values including the Others group
Result showing Top 5 values including the Others group

5. See also

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri