[TABLEAU] Finding The Top N Within A Category

Finding the Top N Within a Category

The Top tab in the Filter dialog box lets you define a filter to show the Top N items based on a specific measure. For example, you can use this type of filter to show the Top 5 Products by Sales.  This type of filter is computed across the entire data source. You can see that the same 5 products are shown within each region because those are the top 5 products sold across all regions. Instead of computing across the entire data source, you can calculate the Top N within a category by creating a set and then manually filtering the values. The example below sets up a filter to compute the Top 5 products within each region. Create the set Step 1 In the Dimension pane, right-click the dimension for which you want to compute the Top N, and select Duplicate. In this example, duplicate the Region. Step 2 Select the copy of Region, hold down the Ctrl key, and select a second dimension. In this example, select both the Region (copy) and Product 3 - Name. Step 3 Right-click one of the selected fields and select Create Set. Step 4 In the Create Set From Selection dialog box, give the set a name. In this example, the set is named Product-Region. Step 5 When finished, click OK. The Product-Region set appears at the bottom of the Data window in the Sets pane. Set up the view Step 1 Drag the original Region (not the copy), the Product-Region set you just created, and Product 3 - Name to the Rows shelf. Step 2 From the Measures pane, drag Sales to the Columns shelf. Step 3 On the Rows shelf, right-click Product-Region and select Sort. Step 4 In the Sort dialog box, select whether to sort Ascending (to show bottom 5) or Descending (to show top 5). For this example, select Descending. Step 5 Under Sort by, select Field. In the Field list, keep the selection of Sales, and in the Aggregation list, keep the selection of Sum. Step 6 When finished, click OK. Step 7 On the Rows shelf, right-click Product-Region and clear the selection of Show Header. Step 8 Right-click Product-Region again and clear the selection of Include in Tooltip (this feature is available only in version 5.x). The view is shown below. Create a custom filter Step 1 From the Measures pane, drag Number of Records to the Rows shelf. Step 2 On the Rows shelf, right-click SUM(Number of Records) and select Measure (Sum) > Minimum. The label on the field changes to MIN(Number of Records). Step 3 Right-click MIN(Number of Records) and select Discrete. Step 4 Right-click MIN(Number of Records) again and select Add Table Calculation. Step 5 In the Table Calculation dialog box, in the Calculation Type list, select Running Total. Step 6 Under Calculation Definition, in the Summarize values using list, keep the selection of Sum. Step 7 In the Running within list, select Product-Region. Step 8 When finished, click OK. Step 9 Hold down the Ctrl key and drag the MIN(Number of Records) field from the Rows shelf to the Level of Detail shelf on the Marks card. Using the Ctrl key in this way copies a field from one shelf to another. Step 10 On the Level of Detail shelf, right-click MIN(Number of Records) and select Continuous. Step 11 On the Level of Detail shelf, right-click MIN(Number of Records) again and select Filter. Step 12 In the Filter dialog box, in the text box on the right, type a number to specify how many values you want to see. In this example, type "5" to see the Top 5 products, etc. Step 13 When finished, click OK. Step 14 On the Rows shelf, right-click MIN(Number of Records) and clear the selection of Show Header. Step 15 Optionally, remove both copies of MIN(Number of Records) from the Tooltip by right-clicking each field and clearing the selection of Include in Tooltip. The final view shown below now shows the Top 5 products within each region.