LOD Expression in Tableau

Hi, This is Part 1 of the Tableau LOD calculation Series. In this series, I will share the concept of LOD Calculation followed by some basic calculations. I will also share how to leverage LOD calculation works, how to write them, and how to solve business problems? Without wasting any time Let’s get started.

In basics, you are using some basic calculations to solve problems and answer easy questions, but in real-world basic calculations are not enough. Sometimes you need to answer the questions which are beyond the scope of the basic level and also the data source & viz level. This is where Tableau LOD expression comes in handy to answer those questions.

So, the First question is, What are LOD expressions?

LOD expressions are those expressions that will be used to calculate value at, the data source level and visualization level. Some will be performed at a more granular level, some will be at a less granular level and some will be entirely independent level.

Types of LOD Expressions: —

1. Include LOD:- Include LOD expressions is used whenever you want to calculate something at a more granular level and aggregate it back to a coarser level. Values will be changed in the Include expression when you add or remove the dimensions from the view.
2. Fixed LOD:- Fixed LOD expression is used when we want our calculation to be completely independent of the view level. (View level means whatever categorical variable or Date function you have put on the worksheet or say whatever you are looking at in the view. It can be none too).
3. Exclude LOD:- Exclude LOD expressions are used when we try to omit something in the view and try to find something at a less granular level.

Let’s see each one in a broader way.

1. Include LOD example: —

The basic calculations will solve problems like:

What are the category-wise sales? But what if you have to answer this type of question like:-

Compare the Avg Customer Sales across different categories.

You can think for a few seconds but couldn’t find the answer, because neither basic calculation nor Table Calculation will answer the question. Don’t worry you are not missing anything just missing the right tool at the right moment and that’s the INCLUDE LOD calc. Let me give you an example in a Tabular form.

Consider this Table. You want the answer to the previous question.

Look at the below table. The Sum of Sales aggregation is performed at the level of customer, a more granular level, and once this level aggregation is complete, the following values are going to be aggregated as an average, resulting in Avg Customer Sales across different Categories. So steps:

Step-1:- Sum happens at granular level dimension(customer name).

Step-2- Then values are reaggregated at the coarser level of the viz(category).

And this will look like this.

This type of situation looks very complex but INCLUDE expression is built for this type of task. Whenever you want to perform a calculation at a finer level of details in the database and reaggregate the value at the broader level of details in your view then you should use the INCLUDE expression.

Let’s look at this in the Tableau

First Drag the category to the column shelf and Sales to the row shelf. I am using the SuperStore Sales dataset.

From the above bar chart you can see the Avg Sales per Category, but is this the solution to our problem. The answer is NO, why because we want Avg Customer Sales per Category, Not Sales per Category.

So, What do we do now? Because basic Calculation failed and Table calculation has no answer to figure it out too. At last, we need to take help from LOD calc. Let LOD do the heavy lifting for you. We will use the INCLUDE LOD expression in this case.

How do we write LOD expressions?

We write LOD calc in curly braces followed by → inside that LOD expression → granularity level at which we want to compute the sales → Semi column → Aggregate measure. Like this

{INCLUDE [Customer Name] : SUM([Sales])}

What does this calc do?

So it will aggregate the Sales at the most granular level we want. In this case, it is customer_name and then it will reaggregate the value at the view level, in this case, which is Category. Bring this calculation to view and change the aggregation to Average by right-clicking or directly holding the right button of the mouse. By bringing the calc it will look like this.

Look at the Green Bar Chart & its values. They are completely different from Orange Bar Chart which is given by the basic calculation. This is the answer we are looking for not the answer shown in the 2nd column by Orange Barchart. So, this is all about INCLUDE expression.

I will talk about other LOD calc in the next series.

Thank you for reading.