Mastering Top N and Bottom N Bar Charts in Tableau: 4 Easy Techniques
Visualize Your Data Like Never Before: Unlocking Insights with Tableau
Building Dynamic Bar Charts: Simplifying Top N and Bottom N Analysis
When building visualizations or charts or dashboards or reports, all experienced developers start thinking about the same questions. What should this report convey? What is the end user interested in? Should we present only the best data points (strength of the data) so that the end users are happy customers? Should we show only the weakest data points which gives room for improvement and further investigation?
How about we show the good points and bad points together? This can be done in many ways. We can show Rank charts or build Highlight tables or Heat maps or KPI shapes etc.
To keep it simple, for this blog, we are going with a simple Top N and Bottom N Bar chart. The number of values ‘N’ can be static or controlled by a Parameter.
Method 1: Utilizing Tableau’s Native Features for Top N and Bottom N Analysis
Method 2: Leveraging Parameters for Interactive Top N and Bottom N Bar Charts
Method 3: Implementing Calculated Fields for Custom Top N and Bottom N Metrics
Method 4: Using Sets to Create Dynamic Top N and Bottom N Bar Charts
For this example, we will be using our favorite Sample – Superstore data set with Orders table.
We are building a very simple Top N and Bottom N Bar chart in Tableau representing Profit for each State, sorted in descending order of Profit with formatted labels. Sorting plays an important role here.
We shall be discussing 4 methods. Two methods will use Helper Table Calculation functions, the third method will use Rank and Window Calculation together and the last one uses conditional sets.
We will be using 4 Helper functions: Index ( ), Size ( ), First ( ) and Last ( ).
The special thing about Helper functions in Tableau is that Helper functions can be on their own without the need of an aggregate measure within the parenthesis.
As and when we start using these functions, we notice that, Tableau will accept them without any aggregated measure. For example, Window Calculation will need an aggregate measure for the calculation to be valid. But Helper functions act as support mechanisms for existing functions. This behavior will be used for our examples.
In the image below, we can see Window_Max() and Window_Sum() have syntax requirements.
Before we jump in, we need to understand the concept of Helper functions very well. This way, it will help us in the future steps. Let us a take a very simple 10 member view. Using a very simple list of 10 alphabets A – J.
Using all the 4 functions; Index ( ), Size ( ), First ( ) and Last ( ). Since all these functions are numbers, they will be treated as measures (or continuous fields). Since all Table Calculations have the feature of Scope and Addressing, it has to be considered for complex cases. For this example, it is the default Table (Across).
1) INDEX – The index function is a very straight forward ID function. So, every member in this view will get an ID in the existing Sort order. This way A is 1 and J is 10.
2) SIZE – The size function provides the total number of members in the view. Similar to Count of Alphabets. So, the total number 10 will be repeated for every cell.
3) FIRST – This can be a little complicated. Here we have to read it from the Left-hand side. We need to assume it as the number of steps ahead from the start line. A is 0 because that is the start. B is -1 because we have taken the first step. Since we have moved forward, the start line is behind us. So, the negative is applied. C is -2 because we have taken two steps and the start line is behind us, giving the negative value. So on and so forth.
4) LAST – Similar to the FIRST function. Here, though we read from the Left-hand side, the focus is on the Right-hand corner (i.e. the end point). We need to assume it as the number of steps remaining to the finish line. J is 0 because that is the finish line. I is +1 because we are one step away from the finish line. Since the finish line is ahead of us the positive is applied. H is +2 because we are two steps away from the finish line and since the finish line is ahead of us, the positive value. So on and so forth.
Keeping all these in mind, we can make combinations of these functions to achieve the Top and Bottom filter.
For this example, we shall create a very simple number selection Parameter. This Parameter will be used to control the view.
Method 1 – Top N and Bottom N in Tableau
1) Create a Calculated field with the required logic. The Calculation will be executed from Top to Bottom because field members are on Rows [** Table(Down) or State].
Here we have 49 states. Assuming the Parameter selection was 5, Index()<= 5 will give the Top 5 states based on the Sort and Size() – Index() < 5 will be 49 – Index(). (*We have to note that for Bottom selection it is < not <=. Because the index of last state is 49. So Size – Index will become 0*). All states from 45 – 49 will be displayed because they are the Bottom 5 states based on the Sort.
2) Using the field on the filter shelf and selecting the required options (Top and Bottom).
After the filter is applied, we get the correct result. But to make it easy to understand, we shall add two more steps for best practices.
Exploring Tableau’s Top N and Bottom N Functionality: A Comprehensive Guide
Interactive Insights: Enhancing Data Exploration with Parameter Controls in Tableau
Custom Metrics: Tailoring Top N and Bottom N Analysis to Your Business Needs
Dynamic Visualizations: Harnessing the Power of Sets for Flexible Data Analysis in Tableau
3) Use a copy of the Top N and Bottom N Filter field on color card
4) If required, use a copy of the Top N and Bottom N Filter field on Rows to the left of State field.
Method 2 – Top N and Bottom N in Tableau
1) Create a Calculated field with the required logic. Again, the Calculation will be executed from Top to Bottom because field members are on Rows [** Table(Down) or State].
Here we have 49 states. Assuming the Parameter selection was 5, First() > – 5 will give the Top 5 states based on the Sort (0,-1,-2,-3,-4 – All these are bigger than -5). Last() < 5 will be last 5 states (4,3,2,1,0).
2) Using the field on the filter shelf and selecting the required options (Top and Bottom).
3) Use a copy of the Top N and Bottom N Filter field on color card
4) If required, use a copy of the Top N and Bottom N Filter field on Rows to the left of State field.
Now that we know the operations and functional behavior of Helper functions, we can mix and match them (Index with Last; First with Size and so on…)
Method 3 – Top N and Bottom N in Tableau
Here we will be using Rank and Window functions. This method is a little complex. But, these kinds of calculations are often required in Tableau. So, it is worth the effort to perform these steps.
1) Create a Calculated field with the required logic
Let us understand this calculation in steps.
a) Rank of the Sum of Profit – It works similar to Index function, but it will depend on the Profit value for each state.
b) Here we have 49 states. Assuming the Parameter selection was 5, Rank(Sum([Profit])) <= 5 will give the Top 5 states. (Rank 1,2,3,4 and 5)
c) Window Maximum of the Rank of Sum of Profit – It works similar to Size function. It will return the highest Rank in the entire view based on the Scope and Addressing.
d) For the 49 states, Window_Max(Rank(Sum([Profit]))) – Rank(Sum([Profit])) < 5 will give the Bottom 5 States (Rank 45, 46, 47, 48 and 49)
2) Using the field on the filter shelf and selecting the required options (Top and Bottom).
3) Use a copy of the Top N and Bottom N Filter field on color card
4) If required, use a copy of the Top N and Bottom N Filter field on Rows to the left of State field.
Method 4 – Top N and Bottom N in Tableau
Here we will be using combined set consisting of conditional sets. We will create one conditional set for Top values and one for Bottom values and combine together. But in this example the difficult part is color legend. If needed, we would have to create our own.
Before we jump in, a brief about sets.
a) Sets are subsets of data
b) Sets can be static or conditional
c) Sets in some cases acts as advanced groups because they are dynamic when conditional. Any change in data will update a conditional set
d) Sets in some cases can be used as filters. They possess the same properties as a Dimension filter and can be used as a placeholder where the filter is applied.
e) Sets create only two buckets – In (Condition is True) or Out (Condition is False).
That is the reason Sets look like Venn Diagrams. We can do Intersection or Union or we can take values without Intersection.
1) Conditional Set 1: Create a Top N set for the required Dimension. In this case State.
We can notice here, it looks exactly like a filter. Also, a new placeholder section called Sets is created in the data pane.
2) Use it on the view (Color Card or Filter shelf) to verify if the set is functioning properly. In this case, the parameter is set to 5. So, the Top 5 States will be colored.
3) Conditional Set 2: Similar to the first step, creating a Set for the Bottom values.
4) Using this set too on the view to verify.
5) Combined Set: Now that we have both the sets ready, we can put them together. In this example, we need a Union (that works like an Outer Join) i.e. collect all members from both the sets where the conditions are True
6) Now this Set can be sued on filters directly. Since it is looking at the True values (IN members), it will automatically remove unwanted values.
We have all the required features. But the end user might need some colors to identify the Top and Bottom values. It is not as easy as the other methods because we had calculations. Here we need to create our own legend.
7) Use both the conditional sets on the Color card.
8) We need to create a Color legend using Character map or any shape palette. The legend can be created as a Text box object on the Dashboard or as a Caption within the worksheet.
