Toan Hoang

TAB114 / Level of Details (LOD)

Toan Hoang

In the final tutorial in this introductory series, we will cover Level of Detail (LOD) expressions, the types of LOD expressions, and how to leverage them within your worksheets.

What is ?

Prior to the addition of LOD expressions to Tableau, data fields are added to a view and then display as values or aggregated as required. As per our last tutorial, we also have the ability to perform Table Calculations which allows us to create fields which are based on the data within a particular view.

However, what if we want to perform a calculation and include a dimension into the calculation which is not a part of the view? How would we handle this? Or perhaps we want to perform a calculation on the items on display but not take into account a certain dimension, again, this could be challenging.

As such, Tableau Level of Detail (LOD) Expressions come in three particular flavours:

In th remainder of this tutorial, we will explore these three types of expressions and get a sense of when we can use these.

Data

To explore Level of Detail (LOD) Expressions, we will load the following data into Tableau Desktop / Public.

RegionCountryCitySales ItemSales
EuropeUnited KingdomLondon1801328
Europe United KingdomLondon2922428
EuropeUnited KingdomLondon3408806
EuropeUnited KingdomLondon4146180
EuropeSpainMadrid1782474
EuropeSpainMadrid287264
EuropeSpainBarcelona1641962
EuropeSpainBarcelona2981043
EuropeGermanyBerlin1351677
EuropeGermanyHamburg1611941
EuropeGermanyMunich1280839
EuropeGermanyFrankfurt160155

Using this simple data set, we will demonstrate FIXED, INCLUDE and EXCLUDE.

FIXED

With the data loaded into Tableau:

You should see the following:

As you can see the SUM(Sales) is Calculated based on the Dimensions that are visible in the View. If we wanted to show the sum of Sales regardless of what is in the View, we can created a FIXED Level of Detail Expression. Create the following:

Total Sales

{ FIXED : SUM([Sales]) }

Total Country Sales

{ FIXED [Country] : SUM([Sales]) }

Now double click the Total Country Sales, and double click Total Global Sales. We should now see the following:

As you can see we have created LOD expressions where we define the level of detail of the Calculation regardless of what is in the current view.

INCLUDE

INCLUDE expressions can be useful when you want to calculate at a fine level of detail in the database and then re-aggregate and show at a more granular level of detail in your view. Fields based on INCLUDE level of detail expressions will change as you add or remove dimensions from the view.

Let us start off by creating the following Worksheet:

You should now see the average Sales by Country.

Image if we want to show the Average Sales by City by Country but do not want to show City, we will need to create an INCLUDE Level of Detail Expression:

Sales (include City)

{ INCLUDE [City] : SUM([Sales]) }

Now let us add Sales (include City) onto Rows and set the type ot Average. Ensure that the Mark Shows AVG(Sales (include City)). You should now see.

So what is this showing? Let us break down Spain for a moment.

As you can see we have included City to the list of Dimensions in the current View calculations.

EXCLUDE

EXCLUDE level of detail expressions are useful for ‘percent of total’ or ‘difference from overall average’ scenarios. They are comparable to such features as Totals and Reference Lines.

EXCLUDE level of detail expression cannot be used in row-level expressions (where there are no dimensions to omit), but can be used to modify either a view level calculation or anything in between (that is, you can use an EXCLUDE calculation to remove dimension from some other level of detail expression).

Let us start off by creating the following worksheet:

You should see the following:

Now imagine that we want to show the Sales in the current view, but exclude a certain Dimension? We will create the following EXCLUDE Level of Detail expression:

Sales (exclude City)

{ EXCLUDE [City] : SUM([Sales]) }

Now double click this newly created Calculated Field to add this to the current view. You should now see the following:

As you can see, we have created a Sum of Sales expression and exclude City from the list of Dimensions.

Considerations

Level of Detail (LOD) Expressions are very useful, but there are several things that should be considered:

Limitations

The following limitations and constraints apply for LOD expressions:

In addition to these, some data sources have complexity limits and return query errors if calculations become too complex.

Aggregation and Level of Detail Expressions

The level of detail of the view determines the number of marks in your view. When you add a level of detail expression to the view, Tableau must reconcile two levels of detail; the one in the view (based on fields added), and the one in your LOD expression(s).

Experiment with LOD expressions and see what happens when you create an expression that returns data that is coarser or finer than the level of detail in your current view. This will allow you to better understand the behaviour of LOD expressions.

Level of Detail Expressions Can Be Dimensions or Measures

When you save a level of detail expression, Tableau adds it to either the Dimensions or the Measures area in the Data pane.

Filters and Level of Detail Expressions

Summary

This was the final part in our introductory series, so I really hope that you enjoyed this series, and more importantly, feel ready to take on the big bad world of Tableau, seriously though, it is nice and warm here. I am always looking for ways to help share knowledge around the community, so if you want to know more about something, and wish there was a tutorial out there, do get in touch on Twitter at @Tableau_Magic.

For a full reference of Level of Detail (LOD) Expressions, check out the following links:

Here is a reminder of our previous articles in our series.

Exit mobile version