Dashboard Element 1: The Current Versus Comparison Index Callout

It also makes the math very easy. A score of 100 means the current performance outperformed the comparison; a score less than 100 means the current performance underperformed the comparison. Since everything is based on a 100-point scale, you simply subtract 100 to compute the change. For example, an index of 120 means a 120 – 100 = 20 point improvement.

The formula for a 100-point index is:

(Current Performance / Comparison Performance) * 100

I prefer a 100-point index to a percentage change formula because I don’t like to compute a percentage of a percentage. However, if you or your stakeholders are used to seeing the data in this format, you can easily convert the index formula to be a percent change formula by changing “* 100” in the preceding calculation to “- 1”.

Now that we’ve isolated the current performance, the prior performance, and the comparison index, we’re ready to make the dashboard element. The only way to combine different fields and different mark types, is to use this trick to conditionally format individual rows or columns in Tableau like you can in Excel. The technique involves setting up a placeholder calculated field with the formula MIN(0); then placing the calculated field on the Rows Shelf three times.

Now that there are three measures on the Rows Shelf, there are three different Marks Shelves that can be edited independently of each other. That means I can put current performance with a mark type of Text on the first row, comparison performance with a mark type of Text on the second row, and the comparison index with a mark type of Square on the third row.

Lastly, I like to color the square based on the 100-point index score. I typically do one color for greater than 100 (outperformed), a second color for a score of 90 – 100 (slightly underperformed), and a third color for a score of less than 90 (underperformed). This can be accomplished with a calculated field such as:

IF [100-Point Index] >= 100 THEN “Good”
ELSEIF [100-Point Index] >= 90 THEN “Okay”
ELSE “Bad”
END

This approach works okay when you’re only making this index callout for a few metrics, but can be a bit manual with all of the calculation creation. There is a way to automate this coloring that is beyond the scope of this post, so I’ll throw in a shameless plug and let you know that it’s outlined in chapter 61 of Practical Tableau. The coloring is much easier if you only want to communicate a positive or negative change; you would simply place the index score on the Color Marks Card and modify the colors so they’re on a diverging color palette, with a step size of two, and a center of 0.

Lastly, the only thing you lose with this placeholder hack is the title of each row. However, you can add that by modifying the text on the Text Marks Card. Here’s how my final dashboard element looks after tweaking the format, adding sheet titles via the Text Marks Card, and coloring the index score by my newly created Index Color calculated field.

An alternative current vs comparison index callout in a Tableau dashboard

I like what we have so far because it displays the exact numbers for the current and comparison performance and it also introduces the pre-attentive attribute of color so my stakeholders can see at a glance how they are doing across key performance indicators. But in the spirit of minimalism, lately I’ve been gravitating towards a slightly different look that collapses the three rows into one.

If you were following along, you already have the calculations and foundation required to create this second look. For this second approach, we will be showing just the current performance and coloring the entire element based on the period versus comparison index. One approach to creating the second view is to remove the first two rows from our first index callout, replace the index score on the label with current performance, and update the element title.

With this style, I like to add either the index point change or percent change following the current performance. As most are new to the 100-point index, this time I’ll show a percent change. The calculation is:

([Current Performance] / [Comp Performance]) – 1

I’ll then place this calculated field on the label by dragging it to the Label Marks Card. I’ll also customize the label so that both numbers are on the same line by clicking on the Label Marks Card and modifying the text.

In this case, the current versus comparison performance was down, but within 10%, so the element turned yellow. If I were to change the comparison to March, where the underperformance was greater than 10% (an index of 90 or less), the element changes to a second color to communicate “Bad”.

If I change the comparison to a month that July outperformed, such as April, the element turns a third color to communicate “Good”.

Whether you prefer the first or second style, I recommend placing a series of these elements across the top or down the left side of your summary dashboard to provide a starting point for your descriptive analysis. Once you’ve got it down, take the percent change formatting a step further by incorporating alt-code triangles into your dashboard elements.

Thanks for reading,
– Ryan