An Introduction to Tableau Level of Detail Expressions

Harness the Power of ‘LOD’s for Better Analyses

Ryan introduces one of the most powerful analytical features of Tableau and shows you how Tableau’s order of operations helps you decide if you should use an EXCLUDE, INCLUDE, or FIXED level of detail expression.

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to give you an introduction to Level of Detail expressions in Tableau. Up to this point, across all the learning paths at Playfair Data TV, we’ve used an important concept that is called the viz level of detail. I think of that as the most granular breakdown of a visualization.

I’ve got this little diagram to help attempt to explain this, but this will make a lot more sense once we just jump in. But to this point, we’ve got what’s called a viz level of detail.

But there is a syntax in Tableau that allows you to have multiple levels of granularity, or levels of detail, within the same view. This kind of helps you control a level of detail across different calculations, so that you’re getting more predictable aggregations, is how I think of it. There’s lots of applications of this.

You will see these in future videos here on the Advanced learning path. But this is just a quick introduction to how to write the syntax, and give you a little bit of an idea of what these are doing. The three different types of Level of Detail expressions are Exclude, Include, and Fixed.

If you were to exclude a level of granularity from the viz level of detail, it would ignore it. It would be less granular. Include is the opposite of that. That would include additional levels of detail– which is where its name comes from– that are even more granular than the viz’s level of detail.

And then, Fixed allows you to fix the level of detail at whatever granularity you would like. To help illustrate, I’m going to put together a bar chart using the Sample – Superstore dataset, that looks at Sales by Category and Sub-Category. At this point, the most granular breakdown is this Sub-Category dimension.

Therefore, whatever other measure I put onto this visualization, it will be broken down by both Category and Sub-Category. If I put Sales on the Columns Shelf again, both the left side and the right side of this chart would look exactly the same. If I were to put Profit on here as a third column, that Profit measure in the third column also gets broken down by Category and then Sub-Category. That is because it’s all three of these columns are sharing the viz level of detail.

The syntax I’m about to share with you allows you to control that level of detail, and even have multiple levels of detail on this same view. For the first example, let’s say that for the second column, we want that Sales measure to ignore the Sub-Category breakdown. So we’re going to start with the Exclude Level of Detail expression.

To do so, these are always used in a calculated field. So I’m going to create a calculated field. And I will call this first one Sales Excluding Sub-Category. And the syntax is open curly bracket, one of three words. So this is where you would type either Exclude, Include or Fixed.

I am, by the way, going to show you an alternative way to write this. But I’m purposely starting with the Exclude Level of Detail expression, because for whatever reason, that’s how my brain thinks about these. And it’s the easiest one for me, personally, to understand.

So, I guess I think that it will also be the easiest for you to understand. But the reason I think it’s the easiest to understand is, I have a very good grasp of what the viz level of detail is. That’s the most granular level of the analysis.

And when you use Exclude, you are just ignoring aspects or dimensions from that viz level of detail. In this case, we’re going to ignore the Sub-Category dimension. So one of three words, we’re starting with EXCLUDE.

What comes next is the dimension that we are excluding. In this case, it is Sub-Category. That’s followed by a colon punctuation mark. What comes next is the aggregation.

For this example, I’m just going to stick with the default, which is SUM, so S-U-M, open bracket– open brace– open parentheses to be precise, followed by whatever measure I am using, so Sales. Close parentheses, close curly bracket, and that is the entire formula for this first example. We’re ignoring the Sub-Category dimension. And the measure we are using is Sum of Sales.

I’m going to click OK. And I’m going to replace the second Sales pill with the newly created version, Sales Excluding Sub-Category. And now notice, the two sides look different.

I’m going to show all the labels, so we can compare the numbers. Notice all the numbers on the right side are repeating. That is because the second measure doesn’t see this Sub-Category breakdown.

So, 742 is the Sum of Sales at the Category level. And it’s just repeating for the Sub-Category breakdown, because it doesn’t know– we’re ignoring it. So it doesn’t know how to break that down, further.

We could very quickly spot check that this is working if we rolled up all of our Sub-Categories into that Category dimension– I’ll just do that by clicking this minus sign, because this is a hierarchy. And now, the left side of the chart should match the right side of the chart. And that is because now, they’re sharing the same level of detail.

The viz level of detail just changed from Sub-Category back to Category, which is less granular. That was the same level of detail being used in our Exclude Level of Detail expression. If I were to expand this again, the right side does not get the breakdown of Sub-Category. The left side does.

There is one more way we could write this. So I’m going to create another calculated field. And this time, I’m going to do Sales with a Fixed LOD expression at the Category level.

And I’m going to start again with that curly bracket. This time, this will be FIXED. And it will be at the Category dimension.

Colon, SUM of Sales again, close parentheses, close curly bracket, I’m going to click OK. And I will add this as a third column. And by default, or at least so far, that second and third column match.

I’ve seen a lot of people get confused with this. They don’t know when to use Exclude versus Fixed, because they’re currently resulting in the exact same answer. Here, I’m going to try to help explain this to you. If we now want to break this down further by say, the Region dimension– so I’m going to drag the Region dimension to the Filters Shelf, and maybe just isolate this to one Region.

Let’s take a look at what happens. So the left side, those numbers got updated. They filtered down to the Central region. They’re being broken down by both the Category, as well as the Sub-Category, dimension.

The second column is ignoring the Sub-Category breakdown. But we saw that number change in the first row for Furniture, from 742 down to 164, when we filtered it just to the Central region. The right side, which is Fixed, is still stuck where it was originally. So the difference of using Exclude versus Fixed comes down to whether or not you want to account for additional breakdowns, that are not part of the Level of Detail expression.

We’re only dealing with our two calculations, with Sub-Category and Category. But in most real world analyses, you probably want to break that down further by some other dimension. When you want to break it down further by another dimension, use Exclude. When you want it to truly be stuck at the Category level forever, regardless of the other dimensional breakdowns, use Fixed instead.

This is a handy guide that I will share in the related content below this video, but this is Tableau’s order of operations. And here’s where Level of Detail expressions fit, within that order of operations. Notice that the Fixed LODs are on that third level. So, they’re the third-highest thing that happened within the order of operations, within the Authoring interface.

The Include and Exclude LODs happen a little bit further. And most importantly, they happen after Dimension filters. Fixed LODs take place, and it sticks the number like that, before the Dimension filters take place in the order of operations.

Include and Exclude have already been filtered on those additional dimensional breakdowns. And then, it then makes it to that Include or Exclude LOD. I’ll explain this more in future videos, particularly on the video that shows you how to create benchmarks in Tableau.

But for now, this has been Ryan with Playfair Data TV – thanks for watching!