Tableau as an analytical tool enables users to organize the data in multiple ways, segment it differently, and analyze it, by using multiple built-in features. Due to the analytical goal of the user, the data can be presented in a way that fits the needs. If the analytical goal of the project is to analyze every dimension member independently, you can use time series as data visualization and monitor the dimension throughout time in order to spot some trends. Or, if your goal is to see many data points in the same space, a scatter plot as a visualization will fit your needs perfectly. Or if you want to make a direct comparison between certain dimensions by the same measure across, for example, sales per product category, a bar chart will serve you well.
Every one of these charts can provide a business value to the user, but usually, we tend to focus more on the ‘top contributors’ that caused the biggest business impact. For example, if we would create a bar chart where we would see the total sales per product sub-category, our main goal won’t be to show thirty bar charts where the difference between the bar with the highest value and the lowest value is huge. We will lean towards the top product sub-categories that were responsible for most of the sales. That way, we can shrink down the range of total values and compare the top N contributors more conveniently.
Fortunately, with a little bit of work around all of this can be done in Tableau. Showing your top N contributors can be done in multiple ways in Tableau. The final decision depends on how much the user of the report puts focus on design, practical aspect, and technical aspects.
We decided to go with parameters as being one of the handiest tools available in Tableau as a result of their flexibility. As the opposite of the filters, parameters transfer the control from the author of the report to the end-user of the report. So, a parameter makes your dashboard user-friendly, but also as an additional benefit, they improve the retention of insight and increase the possibility of it being shared. In this article, we will elaborate on the case of previewing your top N contributors in Tableau by using a dynamic interactive parameter which will be available on your dashboard. We say interactive because the user will be able to choose the exact number of N contributors that need to be previewed. Everything else will be grouped under Other.
Let’s get done to work.
How to Dynamic Display of Your Top N vs Other Contributors in Tableau – Step by step
For the sake of the tutorial, we will use the Superstore Sales sample data that is completely free and comes with each Tableau installation. We will focus on every step of the way, how it should be done technically, and what kind of business value It contributes to the whole process.
Create a Parameter
In order to show the dimension members of the Top N Contributors and group everything else under Other, we need to create a parameter in Tableau that will be used as an interactive control on the dashboard. This way the user can choose how many individual dimensions should be previewed on the dashboard itself. Right-click on the data pane and choose to Create Parameter. Give some name to the Parameter that will indicate that this Parameter will be used for making a distinction between Top N and Other dimensions, for example, ‘Top N vs Other Contributors’. As a data type of the Parameter choose Integer.
Regarding the Allowable values in the Parameter, there are few options available so feel free to choose the one that fits your goal the most. You can choose All that will practically let your user pick any value, with any step size in between. If your data is pretty flexible this solution may be good for you. But for example, if your data has a maximum number of thirty product categories, by not limiting your total range you can somehow ‘trick’ the user into thinking that there may be more sub-categories than expected. In the end, the result will be just an empty view if the user chooses thirty-five top contributors, which will be inconvenient. Or, since the product sub-categories are distinct dimensions, a step size of 0.5 will be not available since you can display just full categories, not half of them. Another option is List. This way you will spend extra time in actually stating the numbers of Top N contributors. And, we don’t want that. So that leaves us with the third option, Range. This way you can choose a range of values that can be chosen from the user of the report and a step size between the numbers. One relevant solution will be to allow values from 3 to 20, with a step size of 1 in between.
Create a Set
Next, we need to choose the dimension that we want to segment. However, you probably chose the dimension that you want to segment and the measure that will be crucial for grouping the dimension on Top Contributors and Others before opening this tutorial. After we choose the dimension, we need to create a set for it. For this tutorial, we will create a set out of the Product Sub-Category dimension. This set aims to show up the Top Product Sub-Categories by Sales measure. In other words, the product categories are responsible for the biggest share of the revenue.
To create a set, you should right-click on the dimension itself, hover over ‘Create’ and choose ‘Set…’. As a result, a new dialog will appear. Here, you can set the conditions by which you choose which states should be included in the set that you are creating. Since we want to group by the Product Sub-Category depending on a specific measure, we should navigate to the third tab of the dialog: Top.
Here, we should select the ‘By field’ option. Here you can choose a specific number that will be kept for this set. The default by Tableau is 10. But since we aim to enable the user to keep changing the number of Top Contributors based on the analytics needs, we can click the dropdown which is located right to the ‘Top’ hard-coded value, and choose the ‘Top N’ parameter that we have just created. This way, the set will keep all values from 3 to 20, with a step size of 1 in between depending on the current selection.
To finalize the set, we want to set the measure that the set will be based on. We will set Sales as a measure.
Create a Calculated Field
The next step is to create a calculated field that will connect the dots in the background. In other words, a calculated field that will navigate the parameter to take only the relevant number of Product categories into consideration, depending on the selection of the user. Here is the formula:
|IF [Top N Categories]THEN “Top ” + str([Sub-Category]) + “Categories”ELSE “Others”END|
Now, we can enjoy the fruits of our work and visualize the calculated dimension as a chart. In order to do that, we will place the calculated field that we have created on the Rows shelf and the Sales measure on the Columns Shelf. As a result, you will get horizontal bars for the top N contributors. Therefore, you should sort the bars by sales.
One thing you should remember is to show the parameter control on the dashboard. You should do that by right-clicking on the Top N parameter and select ‘Show Parameter Control’. By doing this, you are allowing the user of the dashboard to choose how many Top Categories he wants to see, with allowable values of 3 to 20 with a step size of 1.
As a default, we chose the value of the parameter to be 5. That means, when the default value of 5 is selected in the parameter, six bars will appear. Each of the Top Product Categories will have its own bar and one bar will present all other categories which can not be listed as Top Contributors. At this point, it may be a little inconvenient to have the ‘Other’ bar between all others. That’s why, if we want to show the ‘Other’ bar on the bottom, we should switch the places of the dimensions on the Rows Shelf. ‘Top N Categories by Sales’ should be put as a first field. By doing this, we are breaking up the bars whether they are in the Top N or not.
If you are not happy with previewing the In vs Out selection, you can always hide it by right-clicking on the calculation and deselecting ‘Show Header’. Now the user can choose the number of Product Categories that he wants to be displayed and everything else will be grouped under ‘Other’. Here is how our final product looks like!