Online tableau training, tableau tips, & video tutorials

Advanced

Memberships

An Introduction to Level of Detail Expressions

Preview

Instructor

Ryan Sleeper

Learn one of the most powerful analytical features in Tableau

This video provides an introduction to what level of detail is in Tableau and the LOD expressions syntax needed to control it.

This is Ryan with Playfair Data TV. And in this video, I’m going to provide an introduction to a fairly advanced topic called level of detail expressions. There’s lots and lots of applications of these, but this is simply a very high level introduction to help you to try to hopefully help explain what these are and what they do and give you a little bit of the syntax.

To help illustrate, I’ve got this diagram on the screen here. The dashed line represents the viz level of detail. Every single visualization you create in Tableau has what’s called a level of detail. I think of this as the most granular place where the analysis happens. So it’s the most detailed level of the analysis.

If you’re breaking down sales by month, the level of detail is month. If you’re breaking something down by category and subcategory, subcategory is the most detailed aspect of the visualization. With level of detail expressions, you can choose to either ignore portions of the breakdown, to include additional levels of detail, or to fix the granularity of the analysis at whatever level you want.

To help illustrate, I’ve got this image that displays the three different types of level of detail expressions. The first is exclude. That would ignore aspects of the viz level of detail. So it would be a higher level of aggregation.

The second type of LOD– is the short-term slang for it– is include. That would include additional levels of detail that aren’t necessarily being used on the visual. And then the third type is fixed. This is where you can fix that level of detail at different levels within the view.

This doesn’t really make a lot of sense until I just build something and show it to you. So just to show you how to make one of these, use one of these three LOD expressions, I’m going to recreate this view for you.

On the left-hand side, it’s breaking down the sales measure by the category and subcategory. So that’s the viz level of detail. On the right-hand side, we’re ignoring the subcategory breakdown, which is why these numbers are rolled up at a higher aggregation.

I’m going to jump over to Tableau and show this to you. So the measure being used is sales. I’m going to double-click to get that onto the view, I’ll put it onto the columns shelf, and then we’re going to break down that sales measure by two dimensions– category as well as subcategory. And I’ll make this fit the entire view so we can see it a little bit better.

And so now the viz is being broken down– the measure of sales is being broken down by both category and subcategory. If we were to add any other measure as a second column, it would share the same breakdowns. So if I put Profit on as the second column, for example, that Profit measure is going to be broken down by both category as well as subcategory.

What we’re going to create during this introduction to LOD expressions is a calculated field that ignores the subcategory breakdown. So I’m going to start a new calculated field. I’ll call this Sales Excluding Subcategory. In the syntax for every LOD expression is open curly bracket, which you can reach by holding down the Shift key and clicking the square bracket. That creates a curly bracket. Make this a little bit bigger so we can see it.

What comes next is one of three words. These words are the different LOD expressions– either exclude, include, or fixed. In this case, we’re excluding or ignoring the subcategory breakdown. So I’m going to type “Exclude.”

What comes next is the breakdown that you’re ignoring. In this case, it’s the subcategory dimension. That is followed by the colon punctuation sign, then some form of aggregation. I’ll just stick with the default and do SUM for sum followed by the measure that we’re using, which in this case is sales– close bracket, close curly bracket.

So there’s the entire formula for this first example. Just a couple things to point out– you can use include or fixed here. Exclude– the reason I use this in my introduction to this topic is I find these to be the easiest for me to personally understand because I understand viz level of detail. So I understand what is the most granular level of the analysis.

So because I understand that so well, I know I can get there with exclude. All I’m doing is backing out to a higher level of aggregation. So to me it’s a little bit easier to understand this one. But you could use include or you could use fixed. There will be different applications for each of those three.

I also want to point out that you could have multiple dimensions here. We’re only ignoring one thing. If you want to include additional levels of granularity, you can just enter those separated by a comma.

But for now, we’re good. I’m going to click OK. I’ll now replace the right side, so the right column. Instead of Profit, I’ll put our newly created Sales Excluding Subcategory right on top of Profit so that it creates the second column. And it looks like it’s working. I’m going to show all the labels so it’s a little bit easier to visualize what’s happening.

On the left side, the sales number is being broken down at the viz level of detail. So it’s being broken down by both category as well as subcategory. On the right-hand side, we’re still using the sales measure, but it’s only being broken down at the category level. It’s ignoring the subcategory level, which is why we’ve got the same number repeated across each of these three panes.

This would be really easy to spot check and make sure it’s working if we got rid of the subcategory breakdown altogether, if we remove that from the view. Because this is a hierarchy in the sample superstore data set, if I just click this minus sign, the subcategories will be rolled up into the three categories. So I’ll do that. These numbers should match, and they do. Therefore, we know our level of detail calculation is working.

Like I said, there’s lots of applications to this, which we’ll get in future videos. For now, this has just been an introduction.

I’m Ryan with Playfair Data TV – thanks for watching!

Related video: Tableau Classification – Measure Versus Dimension
Related video: An Introduction to the Tableau Marks Shelf / Marks Cards
Related blog post: Tableau Fundamentals – An Introduction to Level of Detail Expressions
Related chapter: Practical Tableau – Chapter 16 – An Introduction to Level of Detail Expressions

Calculated field referenced that ignores the Sub-Category dimension:

{EXCLUDE [Sub-Category]: SUM([Sales])}