Example – Use a Top N Filter to Show the Top Measure Values Based on an Attribute

This example shows how to create a Top N filter using a measure column to show a subset of the highest values for that measure based on an attribute.

In this example, you have a table visualization showing the inventory of some products for a grocery store. There are 10 products in the Dry Goods department and 10 in the Produce department.
Description of filters_topbottomn_example_inventory.png follows
Description of the illustration filters_topbottomn_example_inventory.png

You want to refine the visualization so that it shows the top 4 products in the Produce department based on the number of units available, and you want to target a specific time period.

Option: Add the Top N Filter to the Filter Bar

You can leave these filters at the same filter scope, for example, unpinned as canvas filters. However, if you set the Amount filter to filter on a specific attribute, you won't get the result you want. Here's what happens:

Add the following filters by dragging the columns to the filter bar:
  • Department
  • Date
  • Amount

In this situation where the Top N filter is a canvas (unpinned) filter in the filter bar, it's applied independently of the other canvas filters. The result set of the Top N filter is then combined with an AND operation with the other canvas filters, which results in no data.

  1. Select Produce for the Department. The visualization displays only the products in the Produce department.
    Description of filters_topbottomn_example2_produce.png follows
    Description of the illustration filters_topbottomn_example2_produce.png

  2. Select 2020 Qtr 2 for the Date. The visualization displays only the amount of products that were in the Produce department inventory in the second quarter of 2020.
    Description of filters_topbottomn_example2_date.png follows
    Description of the illustration filters_topbottomn_example2_date.png

  3. Click Amount and in the filter dialog, change the filter type to Top Bottom N. Then type 4 in the Count row and select Product for the By row. The visualization doesn’t display any data.
    Description of filters_topbottomn_example2_amount.png follows
    Description of the illustration filters_topbottomn_example2_amount.png

Now the visualization displays the top 4 products in the Produce department in 2020 Qtr 2 based on the amount of inventory, which is what you’re looking for. This is because the All Attributes in Visual option acts like a visualization filter which means the Top N filter is applied after the other two filters.

Instead, you can de-select Product for the By row in the filter dialog so it uses All Attributes in Visual.
Description of filters_topbottomn_example2_filterbar_ok.png follows
Description of the illustration filters_topbottomn_example2_filterbar_ok.png

Option: Add the Top N Filter Directly to a Visualization

Another way to achieve this is to use Amount as a visualization filter.

  1. Drag the Department and Date columns to the filter bar.
  2. Drag the Amount column to Filters in the Grammar pane for your visualization.
  3. Select Produce for the Department. The visualization displays only the products in the Produce department.
  4. Select 2020 Qtr 2 for the Date. The visualization displays only the amount of products that were in the Produce department inventory in the second quarter of 2020.
  5. In the visualization, click Amount and in the filter dialog, change the filter type to Top Bottom N. Then type 4 in the Count row and select Product for the By row.

The visualization displays the top 4 products in the Produce department in 2020 Qtr 2 based on the amount of inventory. This is because the filters in the filter bar are applied first, then the visualization filter.
Description of filters_topbottomn_example2_vizfilter_ok.png follows
Description of the illustration filters_topbottomn_example2_vizfilter_ok.png