Tableau Dashboard Element: The Current Versus Comparison Callout

An effective ‘Descriptive’ component for comparing any two numbers

Ryan explains two variations of his primary go-to dashboard element. You’ll learn how to format individual cells, create a 100-point index score, compute a percent change, and map calculated outcomes to color values.

Calculated field referenced that creates a 100-point index score:

([Current Performance] / [Comparison Performance]) * 100

Calculated field referenced to compute a percent change:

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

Hi. This is Ryan with Playfair Data TV, and in this video, I’m going to show you one of my very favorite dashboard elements, which is a current versus comparison period index callout. That’s what I call it. But I like to do this with each of my KPIs. Let’s say we have three KPIs. I’ll typically set up one of these elements that I’m about to share for each of those three KPIs.

On the first row, I will show the current performance. On the second row, I will show whatever our comparison point is. Sometimes that’s a goal, if those are defined in the business, but typically, that’s some type of prior performance, because that’s usually what most people have in their dataset. Then on the third row, I will do a little calculation that compares the current performance to that comparison performance in the form of a 100-point index score.

So there’s going to be quite a bit in this video. I’m going to show you some calculated fields. I’m going to show you how to create this element. We’re going to discuss some of the analytics involved with creating a 100-point index score. And I will also show you a variation on this element.

To get started, I’m going to throw out a fake business scenario, and we’re going to pretend that the current month is July, so the seventh month of the year. I’m just using that because I know this will provide a good example later on when we go to add this to a dashboard. I’m also going to take a little bit of a shortcut here and pretend that our Sample Superstore dataset only includes one year of data.

So if I were wanting to isolate the current performance– and we’re going to assume this is July sales, but we’ll call it Current Month Sales– the formula is the aggregation SUM IF MONTH of our Order Date equals seven, then we want Tableau to display the sales amount, END. So there is the formula that will isolate sales for the month of July, but, again, we’re pretending that this dataset only has one year of data because technically, this would be four years worth of July sales. We’re going to call that good for now. I’m going to click OK.

Now that I’ve got Current Month Sales, it’s very easy to replicate this for our comparison. Simply right-click on the calculated field and click “Duplicate.” You’ll see a copy is created. I can now modify that calculation by right-clicking on it and clicking “Edit,” and I just need to make two small changes here.

Instead of calling this Current Month Sales, I’m going to call this Comparison Month Sales. And instead of a seven for the month of Order Date, I’m going to switch that to a six. So we’re pretending that our comp month, in this case, is the month of June, sixth month of the year. I’m going to click OK.

The next thing I’m going to do is lay the framework for this three-row index score, or this dashboard element that we’re creating. This involves a special calculated field that I often create that I call Placeholder. There’s two ways to create this. You could do this in the flow of your analysis by just double-clicking on the Rows Shelf and typing the formula. And the formula is M-I-N, the number zero, and click “Enter.” That’s the whole formula.

That’s one way you could do it that’s very fast, but because I use this so often, I typically go ahead and create a calculated field with that calculation in it. So I call it Placeholder, and the entire formula is the aggregation M-I-N and I wrap the number zero.

The number’s kind of arbitrary. You could choose any number you want, but all this does is it guarantees that the value of this measure is always zero. So it will just draw a y-axis, always on zero, if I’m using the Rows Shelf. I’m going to click OK. You see the new calculated field down there in the Measures area of the Data Pane.

And if I want to create a three-row widget, I need to put this Placeholder field onto the Rows Shelf three times. So I’m going to drag Placeholder to the Rows Shelf a first time. I’ll use my very favorite shortcut in Tableau, which is to hold down the Control key while I click on a field that’s already on the view. And when you click and drag it, it creates a copy of that pill.

So creating a second Placeholder field created a second row. I’m going to hold the Control key down again, click on the second Placeholder field, and drag that next to itself to create a third row. And what’s important about this is this Placeholder calculated field is technically a measure because it always equals zero, which is quantitative. So Tableau has classified that field as a measure.

Now that I’ve got three measures on the view, they each get their own set of Marks Cards over here. And those Marks Cards can be edited independently of each other, which means I can make the first row a mark type of Text, the second row a mark type of Text, and the third row a mark type of Square. And I’ll also click on the Size Marks Card and drag this over to the right on the third row only, just so that square fills all the space in that cell.

We talked about this in another video here a Playfair Data TV, How to Conditionally Format Individual Rows or Columns Like You Can in Excel. We’re going to take this, now, a step further, and I’m going to show you some practical applications of it, which is to create these two different versions of my favorite dashboard element. Now that we’ve got three individual rows, we can place different measures into each one of these cells.

That’s not the default behavior in Tableau. Tableau does not create spreadsheets, like Excel does, where you can click on an individual cell and just type whatever you want in that cell or do some conditional formatting for that individual cell. Tableau doesn’t really work like that, but this little hack with this placeholder field allows us to set up this dashboard component.

So on that first row, I will place my Current Month Sales on the Text Marks Card. And on the second row, I will place my Comparison Month Sales onto the Text Marks Card. And I’ll take these a step further, maybe apply some default formatting to the numbers. These should probably be a currency. I’ll go ahead and make it a currency with no decimal places and click OK.

I also might want to size those a little bit better just so we can see them, maybe bump these up to 14 each. And now we can see this element coming together a little bit. Notice there’s a line in the background for each one of these numbers. It’s because our Placeholder calculation equals zero, and by default Tableau shows what are called ‘zero lines’. So it’s just showing a line on the y-axis where there’s a zero.

I always clean that up at the end. If you want to clean it up, just right-click anywhere in the view and click “Format…”, navigate to the tab for lines, and turn off all the zero lines. And I’ll make sure– and notice there’s also another gray line on that second row. That’s actually a grid line, so if you wanted to turn that one off, you’d also have to navigate to the Rows tab and turn off those grid lines. We’re just cleaning up this box a little bit.

So we’ve got Current Sales in the first row, Comparison Sales in the second row, and this empty blue box at the bottom. For that empty blue box, what I typically fill that with is a 100-point index score. The formula for a 100-point index score is current performance divided by comparison performance times 100. That normalizes everything on the view into a 100-point scale.

So one of the reasons I prefer a 100-point index score to a percent change calculation, like most people do, is it normalizes things across data types. I can be looking at ratios, or currencies, or integers, or decimals, and all of them will be normalized on a 100-point scale. If you’re above 100, you outperformed a comparison. If you’re below 100, you underperformed the comparison.

It also makes the math very easy. Because it’s based on a 100-point scale, you just subtract 100 to figure out what the percent change was. So if I had an index score of 120, that would be a 120 minus 100, or 20-point improvement. If I had a score of 90, that would be 90 minus 100 equals a 10-point decline. So it makes the math very easy.

It requires a another calculated field, and I always call this my Index. And in this case, we are comparing Current Month Sales divided by Comparison Month Sales, and then we are multiplying that by 100. So there’s the entire formula. I’m going to click OK. And I will drag that onto the Label of the third row.

And I don’t usually like to show the decimals with this, so I’m going to change the default properties to show Number Custom and no decimals. I’m going to click OK. And I will also align this in the center of the box and, again, make that font larger so that we can see it.

So you can really start to see this come together now. We’ve got Current Performance on the top row, Comparison Performance on the second row, and then this 100-point index score on the third row. And it’s in a box. But I also like to color that box by the performance.

Usually, this is in stoplight colors. I’m sure you’ve heard that it’s not always great to use red, green, and yellow because of colorblind issues, but that’s how most people do this. I typically try to do at least blue as good, yellow for cautionary or maybe some type of orange, and then red for bad.

I’m going to make up another scenario here that says if the index is between 90 and 100, that’s ‘cautionary’. If it’s above 100, that’s ‘good’ because we outperformed the comparison. If it’s between 90 and 100, it’s OK. And if it’s below 90, it’s ‘alarming’. We want to pay attention to that.

So I’m going to make a calculated field that colors that box by these different thresholds. I’ll call this my Index Color. And the formula would be IF our Index score is greater than or equal to 100, then we’ll say that’s ‘Good’. ELSEIF that Index score is above or equal to 90, then it’s just ‘Okay’, but it’s cautionary. Maybe we’ll color that some yellow or orange just to bring some attention to it.

And if it’s not one of those first two, so if it’s not above 100, or not above 90, the only thing that’s left is everything below 90. So that would be everything that is ‘Bad’. And I’m going to click END. I left off an IF at the very beginning.

So this is just some simple IF / THEN logic saying if the index score is greater than or equal to 100, that’s Good. Otherwise, or a second criteria or logical statement here, is if that index is greater than or equal to 90, we’re going to call that Okay, and everything else is Bad. I’m going to click OK and drag my Index Color to the Color Marks Card.

We just see one color because it can only be one thing at a time. So it’s Okay. I will remap a yellow color to Okay. We will see Good and Bad later on in this tutorial, but by default, it’s Okay, so I’m going to color that yellow and click OK.

So we’ve almost got this first component. There is one more thing I’d like to do to clean this up a little bit. This is looking good. All the formulas are working, but I don’t know what is in each box.

So I’m going to go back to the Text Marks Card for each one of those and click on the Text Marks Card. And after clicking this ellipsis, this little word processor will open where I can use a mix of hard-coded and dynamic text. The gray shading indicates that it’s a dynamic value being shown, so that’s being generated by our calculated field. But below that, I’ve now got some freedom to tell the end user what’s in this box.

I’ll call this AUGUST SALES, and that probably doesn’t need to be as big. Click OK. And now AUGUST SALES is in that box. I’ll go to the second row and update that to say JULY SALES– and, oops, actually this is June sales in this box. And it was July sales in the first box.

But I’m just telling my end user what’s on each one of these rows. And on that third row, I will modify this label and say this is the INDEX SCORE. And lastly, those Placeholder headers mean absolutely nothing, so I’m going to hide those by right-clicking on any of them and deselecting “Show Header.” And now we’ve got this nice three-row widget working for us.

I typically will place this along the top of a dashboard. So again, like I mentioned in the introduction to this video, if I’ve got three KPIs, I’ll go through these steps to make one of these for each of my three KPIs. And it just gives my end users a very good, descriptive insight to begin the analysis. We’re now going to take this a step further, and I’m going to show you a variation of this.

I typically like to streamline this idea, or these numbers, into one row. I’ve been doing this a lot lately. When I started with this feature, I’d always use the three rows, but I’ve found a way to do this in a little bit more of a minimalist style, still conveying the same information but making it more streamlined.

What we’re going to do is display just one cell. We’re going to show the current performance, and then we’re going to color the box by its comparison performance. And we’re also going to add what the percent change was. We’ve already gone through all of the steps to create all the data that we need, so the easiest way to get to this variation is to remove the first two rows.

So I’m going to remove the first Placeholder and the second Placeholder and that leaves me with just the box with our colored index score. Instead of showing that index score as the label, I’m going to put my Current Month Sales onto Label. I’ll drop it right on top of the Index Score so it just gets replaced.

I will also display the percent change because that’s how most people look at it. So part of the reason I’m explaining this is if I didn’t sell you on the value of using a 100-point index score, I’m going to show you how easy it is to change our 100-point index score to a percent change calculation. I’m going to duplicate our Index calculated field, and I will call this one our Percent Change.

And the first part of the formula is actually the same. It’s the current performance divided by the comparison performance, but the difference to convert this to a percent change is to change that ‘times 100’ to ‘minus one’. That’s it. It’s as easy as that to change a 100-point index score to a percent change calculation. I’m going to click OK.

I also want to change the format of that percent change formula. Remember, for Index, we made that number custom with no decimals. For Percent Change, I want to change the default properties so that this is a percentage with no decimals. I’m going to click OK. And I will add that to the label.

We also no longer need the INDEX SCORE text. So to clean up the Text Marks Card– or in this case it’s the Label Marks Card– I’m going to click on the Label Marks Card, click on this ellipsis, and make a couple of small changes. We don’t need INDEX SCORE, so I’m going to delete that. And I’ll also put the Percent Change after– so I’m cutting and pasting– I’m going to put our Percent Change after our Current Month’s Sales in parentheses. And we’ll make this all the same size text. Click OK.

And maybe I should still say at the top what this KPI is. So this is, I’ll say, CURRENT MONTH SALES this time so I don’t get the months confused, and I’ll click OK. And now, we’ve got this nice, streamlined widget. It’s showing the Current Month Sales value. In parentheses, it’s showing the month-over-month change, in this case. And then that box is colored by that same logic that, if it’s within 10% of the comparison, it’s yellow, like we see here. If it outperformed the comparison, it’ll turn blue, and if it’s under the comparison, it’ll turn red.

Just to show you a couple of variations to finalize this view, I’m going to change the month that’s being used in our comparison. Instead of June, I’m going to switch this to March and click OK. And notice it turned blue. You can see here we had a 28% decline this time, so it’s outside of our 10% threshold that makes it Okay. It’s now bad. I’m going to remap this color to red.

And I’ll show you one more variation for comparison month. If I bump this up to April and click OK, the current month now outperformed the comparison month by 7%. We had 7% growth. So again, hopefully, your wheels are turning and you’re seeing what’s happening here.

As I’m changing the comparison month, it’s looking at that index score that we did earlier, seeing where it’s at. If it’s within 10%, it’s supposed to be colored yellow. If it’s outside of 10% in the negative direction, it should be colored red. And if we outperform the comparison, it should be colored green– or blue in this case.

You have to remap those colors once, but from now on, and into the future, as this is scaling and our data is updating, this widget will automatically update for us. So if I were to change my comparison back to March and click OK, now it’s red because we had already mapped that red color. See the Current Month Sales, we see we have a 28% decline, and it’s colored red to immediately bring attention to this.

And again, this is just two variations of one of my favorite dashboard elements. I would consider this descriptive, but it gives myself, as well as my end users, a very good starting point for the analysis and tells me what to focus on that day.

This has been Ryan with Playfair Data TV – thanks for watching!