Exercise: Use LODs to Compare Sales to a Benchmark

Top 10 State Sales Compared to Florida Sales

In this self-guided exercise, you will use an EXCLUDE level of detail calculation to compare the sales performance of the top 10 states in the Sample – Superstore dataset to a benchmark.

Hi. This is Ryan with Playfair Data TV. And in this self-guided exercise, you’re going to be using Level of Detail expressions to create benchmarks in Tableau. What I’d like you to try to do is recreate this chart type that you see on the screen. The left side is very easy. It’s simply Sales by State. These are sorted in descending order. And I’ve filtered it to just be the top 10 States.

On the right side, it’s a little bit trickier. We’re going to practice making benchmarks in Tableau. In this fake use case, we’re pretending that Florida is our benchmark state, and we’re going to compare every state’s performance to the performance of the state of Florida.

I’ll give you two clues on this, and then what you’ll do is pause the video, try to take a shot at it, and then when you’re ready for me to explain it to you, hit the Play button. Two hints, you’re going to need two different calculations.

The first calculation is going to isolate sales for the state of Florida, but it’s not as simple as just saying SUM IF State equals Florida, then Sales. You need to use a Level of Detail expression to get the sales for the state of Florida on every row in the data set so that Tableau can do the math, each state compared to Florida sales.

The second calculation you need will take SUM of Sales, which will be computed at the viz level of detail, minus that newly-created calculated field from step 1. So take a shot at that. When you’re ready, just hit the Play button.

All right. Let’s see how this was done. Whenever I’m doing something new in Tableau, I always try to break it into smaller elements. So half this chart is very much review for me. It’s very basic. So let’s just knock that out right away.

We’ve got Sales by State. The orientation was flipped, which you can do by clicking this Swap button under the word Server in the top navigation. We were sorting, so I’ll click this one-click option under the word Window in the top navigation, and it was filtered to our top 10.

There’s a couple of ways to do this. I’ll just do a quick multi-select to choose my top 10 dimension members on the view, and I’ll click Keep Only. We also don’t need the Country dimension on the Rows Shelf, so I’ll just remove that. And lastly, I’ll change the fit of the view to Entire View just so we can see this a little bit better.

So that part, simple. Now, we’re going to create a benchmark that isolates sales for the state of Florida. To do that, I need to set up a calculated field, and I’ll call this Florida Sales. There’s three ways to do this. The first way– which would be the wrong way, by the way– is to do SUM IF State equals Florida, then Sales.

The reason that’s the wrong way is that Sales value would only show up on one line. If you want more explanation on that, see the video called “How to Make Benchmarks using Level of Detail Expressions in Tableau.” That will explain more about that.

You could also set this formula up with a FIXED level of detail expression. My preference when creating benchmarks is to use the EXCLUDE level of detail expression. So the formula is open curly bracket, the word EXCLUDE. What comes next is whatever dimension I’m ignoring. So we are ignoring the State dimension, so I’ll type State, a colon punctuation mark, my aggregation, which will be SUM.

And we’ll say, if State equals Florida, then Sales, the word END. Close bracket, close curly bracket. So we’re saying ignore the State dimension. Any time the State dimension member equals Florida, then show the sales value. Click OK.

Just to show you what this calculation does, I’ll put it on as a second column. And we see the performance for the state of Florida on every row. This is what’s going to allow Tableau to do the math of each individual state versus our benchmark, which is the state of Florida. So it would be a little bit easier to see if I toggle these labels on. Notice every bar on the right-hand side equals the sales performance from the state of Florida.

The second calculation we needed was to take the sales at the viz level of detail, which in this case is State, minus our benchmark, which is Florida Sales. And the formula is SUM of Sales minus SUM of Florida Sales. Click OK. I will replace that right side with the Sales minus Florida Sales calculation, and we can see it seems like it’s doing this correctly.

Some of the values were higher than Florida, which we know because Florida is ranked 6th out of 10 here, and then the other four are below Florida. So it seems like it’s working. We could, of course, do the math if we wanted to, just to quality check this, but I’m going to assume it’s good.

And the last couple of things were to do some coloring on this. The left side, let’s just make a neutral color like a gray, maybe a little bit darker than that. And on the right side, we will color those bars based on whether they are higher or lower than the state of Florida.

I’m going to navigate to the second column of marks, and I’m going to use my very favorite shortcut in Tableau, which is to hold down the Control key while I click on a pill that’s on the view. Now, if I drag that to color, it creates a copy of that pill.

And we see our color legend show up. Maybe I will edit these and simplify the colors a little bit. I’ll change the step size to 2 and also pick some brighter, maybe more pastel colors for this. Instead of this darker red, I’ll choose orange for negative. Instead of the darker blue, I’ll choose this lighter blue for positive.

Click OK. And we’ve just made this two-column bar chart. On the left, was the sales performance per each state, my top 10. On the right, is the sales performance compared to our benchmark, which in this case, was the state of Florida.

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