Highlight min & max values in a column in Tableau
If you are just exploring Tableau, then I’m back with another scenario you could probably face while building a Tableau dashboard or could be asked in an interview.
To make things easier, let’s stick to the dataset that comes with Tableau.
Scenario: Need to highlight a month with minimum revenue and month with maximum revenue for every sub category.
Let’s list the steps to achieve the same:
1. Place Sub-Category in Columns and Order Date in Rows. By default, it is set to showcase the year of the Order Date. Right click and select Month and ensure it is discrete, if not.
2. Place Sales in the Text Mark card so that we can view the sales of each sub category for every month.
3. Create a calculation field and let’s name it ‘Index’. Inside the field just type INDEX() and click OK. This is essential to define the order of any further calculation.
4. Now place the Index calculation field in Details Marks Card. Right click and select ‘Edit Table Calculation’. Select ‘Specific Dimension’ and check the 2 options appearing: Sub-Category and Month of Order Date.
5. Create another calculation field and let’s name it ‘Color min and max’. Inside the calculation field type the following:
If [Index] = 1 then “Green” //to highlight the month with maximum revenue
Elseif [Index] = 12 then “Red” //to highlight the month with minimum revenue
Else “White” //to highlight the remaining
END
Note: We mentioned 12 in the calculation. There are 12 months in a year and once the Table calculations are set as described in the next step, the month which has the lowest sales will get highlighted as Red. Also, do ensure you have white in the Color Palette or select any light color available in Color Palette.
6. Proceed to place the calculation field ‘Color min and max’ on the Color Marks card. Right click and select ‘Edit Table Calculation’. Select ‘Specific Dimensions’ and ensure that both the options — Sub-Category and Month of Order Date — are selected.
We will then check if the “At the level’ value is selected as ‘Deepest’. We have another option ‘Month of Order Date’ but we will not opt for the same as we want the calculation to happen at both levels: Month of Order Date as well as Sub-Category.
Moving on to the next, we can see “Restarting every”. Here we can see 2 options: None and Sub-Category. We will select Sub-Category. The reason for this is that once the month with highest sales and month with lowest sales is highlighted for the first Sub-Category, the calculation has to be repeated for another Sub-Category and so on and so forth. In other words, the calculation starts again as soon as there is a change in Sub-Category value.
Last but not the least, we then see ‘Sort order’. There are 2 options here: Specific dimensions and Custom. Select ‘Custom’ because we want to define our own sort order. Once ‘Custom’ is selected 2 drop-downs are visible. In the first drop-down, select ‘Sales’ and in the other drop-down select the aggregation required. In this case, it is SUM. Below opt for sort order needed which is Descending as we need to highlight the month with the highest sales as Green. This is possible only if we allow Tableau to set the Index as 1 for the month with highest sales.
7. Do the final formatting changes for a more clean and organised output:
a. Click on the Color tab and ensure that the color of min, max and otherwise is set correctly
b. Above the Columns bar, select ‘Entire View’ from the drop-down.
c. In the Marks Card drop-down, change from Automatic to ‘Square’. Increase the size accordingly.
d. In the Label Text Card, under ‘Alignment’, set ‘Horizontal’ as ‘Center’ and ‘Vertical’ as ‘middle’.
8. In the Marks Card drop-down, change from Automatic to ‘Square’. Increase the size accordingly. The result will be somewhat similar to the image below.
Personally, I would recommend exploring the options in Table calculations and checking out the output. For example, swap the Rows and Columns or simply do CTRL+W. To get the desired output, check where all a change is required. HINT: Apart from obvious changes, the order of fields appearing in ‘Specific Dimensions’ needs to change.
I was scared of Table calculations for a long time. But nothing like exploring these options further to get used to Table calculations: understanding their functionality and becoming comfortable using them. With this, I have reached the end of this blog post. Thanks for reading and hope your experience with Table Calculations is smooth.
