Tableau Tip: How to Dynamically Display the Top N vs Other

Next, create a set for the dimension you want to segment. For this tutorial, I will create a set out of the State dimension in the Sample – Superstore dataset. The set will be set up to show the top N states by the Profit measure.

To start a set, right-click on the dimension, hover over Create, and click “Set…”. A new dialog will appear where you can set the conditions for which states should be included in the set. Since we are wanting to keep the “top N” based on a specific measure, navigate to the third tab: Top.

Here you can choose a specific number to keep (the default is 10), but you can also click the dropdown located to the right of the hard-coded value and choose our newly created “top N” parameter. Based on our parameter settings, this will keep 5, 10, 15, or 20 states in the set, depending on the current selection.

To finalize the set, we’ll also want to change the measure that the set is based on to Profit. Here’s how my set looks at this point.

The next step is to create a calculated field that will do the desired grouping. Here is the formula:

Top N vs Other States by Profit = IF [Top N States by Profit] THEN [State] ELSE “Other” END

Now we’re ready to use this calculated dimension in a chart. I’ll place this new calculation on the Rows Shelf, the Profit measure on the Columns Shelf, and sort the bars by profit. Also, be sure to show the parameter control by right-clicking on the top N parameter and choosing “Show Parameter Control”. This will allow you and the end user to choose between the allowable choices of 5, 10, 15, and 20.

As you can see, when the default parameter value of 5 is selected, there are six bars; one for each of the top 5 states by profit and one for everything else. If you always want the “Other” bar on the bottom, place the “Top N States by Profit” set we created earlier as the first field on the Rows Shelf. This will break up the sets of bars depending on whether or not they’re in the top N.

You can always hide the first header for In vs Out by right-clicking on it and deselecting “Show Header”. Now the end user can choose to group the top 5, 10, 15, or 20 and everything else into it’s own segment called “Other”! Here’s how my final view looks after cleaning up the formatting and changing the top N parameter to 10.

Thanks for reading,
– Ryan