How to Use LOD Expressions to Create Benchmarks

Harness the power of level of detail calculations to create comparisons

Learn how to use the FIXED and EXCLUDE level of detail expressions to create benchmarks such as prior date period, a specific dimension member, or a competitor to help analyze your company’s performance.

Hi. This is Ryan with Playfair Data TV, and in this video, I’m going to give you an example of one of my favorite ways to use level of detail expressions, which is to use them to create benchmarks. I think this will also help you wrap your head around this pretty advanced topic, level of detail expressions, because I’m going to show you some more detail about how these kind of tick. And we’ll also talk a little bit about the filter and level of detail order of operations.

To give you a basic example of setting up a benchmark, I’m going to set up a quick table that looks at Sales by Year of Order Date. And I know it’s the dreaded text table, but I think this will help illustrate the raw numbers. So that is what I’m going to start with here. We’ll make it better in a little while.

But right now, we’ve just got this text table, Sales by Year of Order Date. And just to throw out a quick, fake example, let’s say that in our business we consider 2015 to be our benchmark year, for whatever reason. That was the first year in the business.

Let’s just say we know that if and when we ever dip below that performance, maybe we should think about a different strategy or get out of the business. Who knows. But for now, we’ll just pretend that 2015 is the benchmark.

In a real business, this could be a competitor. This could be your best performance. There’s lots of things you could benchmark from, but for this example, we’ll say 2015 is our benchmark year.

Let’s say that in this table we wanted to compare every individual year’s performance to the year 2015. Your first instinct might be to set up a calculated field, and I’ll call this 2015 Sales, and the formula would be SUM IF YEAR([Order Date]) equals 2015, then Sales END.

So that’s what your instinct might be if you were wanting to isolate the performance during the year 2015. I’ll click OK. I’ll double click to add that to the view, and notice we only see it show up in one place. And I guess just to make this a apples-to-apples comparison, I’ll change the number format as well just to get that to match up.

We only see 2015 Sales in one year because Tableau is going to run each of these four dimension members through this calculation saying, if the year equals 2015, then Sales. Well, that statement is only true in the first case. That’s why we’re seeing nulls for the others.

And what makes this difficult to do our analysis is in order to do a comparison, each year’s sales minus our benchmark sales, these numbers have to be on the same row or column in order for us to do that. But right now, the 2015 Sales is null on three out of our four years, so it can’t do the math.

Let me show you what I mean by that with another calculated field that looks at Sales minus 2015 Sales. And the formula would be SUM of Sales at the viz level of detail, which in this case would be Year of Order Date minus 2015 Sales. And notice we don’t have to aggregate the second one because it’s already being aggregated in the underlying formula. I’m going to click OK and double click to add that as a third row.

And notice it can only do the math on the year 2015. It’s taking $484,247 minus $484,247 to get to 0. I explained this just to point out this is why you need level of detail expressions in order to get this to work. We’re going to ignore different aspects of the viz level of detail in order to get that $484,247 number to show up on every column. And when we can get it in that state, then we can allow Tableau to do the math. Each year’s sales minus the sales from 2015.

There are two ways to create this benchmark. I’m going to show you my preferred way first, hoping that that’s what you commit to your muscle memory. I’m going to create a calculated field, and I’ll call this 2015 Sales with EXCLUDE LOD. And again, this is my preferred way to set this up, but the formula is open curly bracket EXCLUDE– whatever dimension you’re ignoring; in this case it’s Order Date– the colon punctuation mark, the aggregation comes next, and then what we had previously typed out, IF YEAR([Order Date]) equals 2015, then Sales. END. Close bracket, close curly bracket.

So this is my preferred way to make a benchmark. What this is going to do is ignore the year of order date and show the sales if the year was 2015. I’m going to click OK, and double click on that to add it to the view. And again, I’ll make this the same currency formatting so it’s a little easier to compare. There we go.

Now notice on this third row that we see that $484,247 number in each column. It’s ignoring each individual year and always displaying the value from the year 2015. Now that that number is in every column, we can do the math. I’m going to edit my Sales minus 2015 Sales and replace 2015 Sales with our newly created EXCLUDE LOD.

You’ve got two options for the aggregation of a level of detail expression. Notice we’ve got an error and what it’s saying is we can’t mix aggregates and non-aggregates. If you think back to our formula for 2015 Sales with EXCLUDE LOD, and in fact, you don’t have to think back to it. I can even just show it to you.

If you click on anything that’s orange in a calculated field dialog box and click this right arrow, if that field is a calculated field, you’ll see what the underlying formula is here on the right-hand side. Well, one of the things I see in that underlying formula is the aggregation of SUM. So it seems like it’s already being aggregated, but level of detail expressions are an exception to aggregations. This one actually does not count. You need to aggregate this one more time. You’ve got two options on how you can aggregate it.

You can type ATTR and wrap this entire thing. That fixes the error message. Or you can just type the same aggregation a second time. So instead of ATTR, I could type SUM, and both of these will work in this example.

Well, we’ve just replaced our original 2015 Sales measure with our newly-created 2015 Sales measure that uses the EXCLUDE level of detail expression. Now, if I click OK, Tableau is able to do the math because both numbers are on the same column. So $470,533 minus $484,247, we lost $13,715.

I’m going to show you one other way to make this benchmark using a FIXED LOD, but I’m also going to point out why I prefer the EXCLUDE LOD instead. So I’ll make one more version, and I’ll call this 2015 Sales with FIXED LOD. And the formula is open curly bracket. We’re not actually going to include any dimension. We’re not fixing it at any specific level. We’re just fixing it for the entire data set.

So we actually don’t even need to type the word FIXED in this case. I could just type the aggregation parentheses and then one more time, IF YEAR([Order Date]) equals 2015, then Sales. END. Close bracket, closed curly bracket. So this is fixing this value at the entire data set. If the year equals 2015, it’s going to show sales. I click OK, and double click on that to add it to the view. Let me change the format one more time for you.

And you can see that both in the EXCLUDE version and the FIXED version that the number for 2015 Sales is in every column. So we would be able to do the math. However, what’s different and a little less predictable about that second version is, as I go to add other filters, so if I was wanting to do any type of benchmarking that included a filter other than year, watch what happens.

If I drag Segment to the Filters Shelf and choose Corporate, for example, and click OK, notice we’ve got different numbers now. The version with the EXCLUDE LOD is matching 2015 Sales, how I would expect it to. It’s been filtered down to the Corporate segment.

The FIXED LOD, we’re left with the original metric as if nothing had been filtered. That’s because FIXED level of detail expressions happen before Dimension filters in the data set. So it’s actually ignoring that filter and giving us the answer for the data set as a whole.

That’s not quite what I wanted. It’s a little more predictable to use this EXCLUDE LOD because it will account for any other dimension member or any other dimension filter that you add to the view. So that EXCLUDE one is my preferred way to make my benchmarks.

To clean this up a little bit and just close the video by making this a little bit more useful chart, I’m going to remove that filter and only include Sales and then comparison to my 2015 benchmark. So I’ve got these two numbers on the view for each year. I’ll go over here to Show Me and convert this into a chart. What I’m really trying to do is just get these measure values onto the Rows Shelf. So temporarily, I’ll just quickly combine this into a dual combination chart. That puts my Sales as well as my Sales minus 2015 Sales measures onto the Rows Shelf.

But from here, I’ll make a couple of changes. I’ll change this to discrete, which will make my sizing of the bars look a little bit better. And instead of a dual axis, a combination chart, I’m going to make a bar in bar chart, which you can do by changing the mark type of the line to Bar. And I’ll make this a little bit skinnier. Remove my colors momentarily.

And maybe for the bars on the left, which represent my sales values, I’ll make that some neutral color like a gray. And for the bars on the right side of this chart, I’ll color those by their performance compared to our benchmark. I’ll use my very favorite shortcut in Tableau, which is to hold down the Control key and drag this measure to the Color Marks Card.

When you hold down the Control key, it creates a copy of that pill. I’ll also clean up this color legend, maybe just make this two steps. We’ll assume those colors are good. Click OK. And lastly, I will synchronize the axes and hide this header on the right.

So a little bit more useful chart, but just one of infinite examples of how to use a benchmark in Tableau. And I wanted to show you how I personally use level of detail expressions, specifically the EXCLUDE level detail expressions to help me make my benchmarks.

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