Level of Detail Calculations in Tableau Part 2: Fixed, Include and Exclude

Today, we're going to explore the 3 types of Level of Detail (LoD) Calculations in Tableau.  They each have their own strengths and weaknesses.

LoD Type 1: FIXED

FIXED is arguably the simplest LoD to use in Tableau.  It calculates the same value, the same way regardless of the viz you put it in.  Let's see a simple example.  We start with a chart that shows Sales by Category and Sub-category.

Sales by Category and Sub-category

Let's try to append Total Sales onto this chart.  Total Sales is a fixed calculation because it doesn't care which fields are in the chart.  It wants ALL sales.  The syntax for an LoD is as follows:

{ (LoD Type) [Dimension1], [Dimension2], etc. : Metric }

Overall Total Sales

Sales and Overall Total Sales by Category and Sub-category

Since we want Overall Total Sales, our LoD Type is going to be FIXED, we don't have any dimensions, and our metric is SUM( [Sales] ).  As you can see, this is quite simple to write and works perfectly when you drag it on the chart.  If you read Part 1, you will remember that high-level aggregations like this are not affecting by the outside aggregation.  Therefore, changing the SUM() to a MAX() does not affect the value in the chart.  We'll touch more on that in a later post.

Sales and Overall Total Sales by Category and Sub-Category (with MAX())

LoD Type 2: INCLUDE

INCLUDE is a slightly more advanced type of LoD.  An Include LoD looks at all of the dimensions in your chart, and adds another dimension to the list.  For instance, let's look at Sales by Category.

Sales by Category

Now, what if we wanted to know the Sales for the largest Sub-category within each Category.  We can simply INCLUDE Sub-category to solve this.

Sales of Largest Sub-category

This calculation will take the Dimensions in the chart (Category) and combine with the included Dimensions (Sub-category) to create a table of SUM( [Sales] ) by Category and Sub-category.

Sales by Category and Sub-category (again)

Now, if we set the overlying aggregation to be MAX(), it will take the maximum Sub-category Sales within each Category.  This is what we see on our final chart.

Sales of Largest Sub-category by Category

A quick comparison to the LoD table confirms that our calculations are correct.

Sales by Category and Sub-category (with Largest Sub-categories highlighted)

An astute reader might note that this calculation could also have been done using

{FIXED [Category], [Sub-category]: SUM( [Sales] )}

This is correct.  In fact, most of the simple calculations can be done using a number of different calculations.  The real differences come when the calculations get more advanced.  But that's for a later post.

LoD Type 3: EXCLUDE

EXCLUDE works the exact opposite of INCLUDE.  It removes a dimension that currently exists in your viz.  For instance, let's start with a chart of Sales by Category and Sub-category.

Sales by Category and Sub-category (for the 3rd time)

We want to calculate the Total Sales within each Category.

Total Category Sales

Sales and Total Category Sales by Category and Sub-category

It should be noted that when you are using INCLUDE and EXCLUDE, the value of the LoD may change if you change your viz.  For instance, let's take Category out of the above chart.

Sales and Total Category Sales by Sub-category (or not)

Using EXCLUDE to remove Sub-category now leaves us with no dimensions, which causes the Total Category Sales to reflect Overall Total Sales instead.  For this reason, it's generally a good idea to accurately name your calculations.  For instance, "Total Category Sales" should be "Sales Excluding Sub-category".

The final key difference between these 3 LoD Types is how they interact with filters.  FIXED LoDs are designed to work independently of the chart.  Therefore, their values are not affected by traditional filters.  They see what the chart sees (also known as the context).  However, INCLUDE and EXCLUDE LoD's are designed to work in the confines of a single viz.  Therefore, they are affected by traditional filters.  This leaves us with the following order of calculation.

1) Data Source Filters

2) Context Filters

3) FIXED LoDs

4) Traditional Filters

5) INCLUDE and EXCLUDE LoDs

We still have a tremendous amount of room to explore the great new feature of Level of Detail Calculations.  Thanks for reading.  We hope you found this informative.

The workbook for this post can be found here.

P.S.

A couple users on the Tableau forums pointed me towards this graphic which I think tells the story perfectly.

Brad Llewellyn
Director, Consumer Sciences
Consumer Orbit
llewellyn.wb@gmail.com

http://www.linkedin.com/in/bradllewellyn