 In our previous blogs on LOD in Tableau we covered the concepts of Include and Fixed LOD expressions and their use cases. In this blog, we will focus on Exclude LOD in Tableau.

What is Exclude LOD?

Computations are performed for all dimensions present in the view except for the dimension(s) mentioned in the expression.

{Exclude [Dimension1], [Dimension2] … [Dimension n]: Aggregation}

Let’s understand Exclude LOD through an example.

Build the following view: Image 1

Currently sales are being computed at Region and State level.

Now add city to the view: Image 2

In the above image the Sales are being computed at Region, State and City level.

Create the Exclude LOD calculation and add it to the view.

{EXCLUDE [City] : sum([Sales]) } Image 3

The column with Exclude expression is different from the Sales column.

Since we have mentioned in the LOD calculation to Exclude City dimension when computing the sales, the sales is computed only at Region and State level. This can be verified by comparing sales value of Illinois state in Image 3 and Image 2.

Let’s look at applying Exclude LOD expression in a real-time scenario.

Scenario:

We need to find out the difference between Sales value of two Sub-categories with the flexibility of choosing the Sub-Category of our interest as the reference.

Solution:

Build a Sales by Sub-Category bar chart.

Create a Parameter with field values from Sub-Category and create a calculation as shown below: Image 4

Adding above expression to the view we get following output: Image 5

As mentioned in the calculation the sales of only the selected parameter value (in this case Accessories) is shown.

Create the following expression:

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

This expression does the computation by not calculating sales for every Sub-Category.

Since Sub-Category is the only dimension present in the view adding the exclude expression gives an output that would be as same as using the expression in a sheet for selected parameter without any dimensions. The output is shown below: Image 6

Adding the expression to the previous view we get the following output: Image 7

We can see the values are the same as in Image 6.

We can subtract the Exclude expression calculation’s output with actual sales of every category. This will show the difference between sales of Accessories Sub-Category and other Sub-Categories.

Sum([Sales])-Sum ([Exclude Sub Category])

Adding this to the view we can easily compare the difference in sales between a selected Sub-Category from parameter and other Sub-Categories. Image 8

In subsequent blogs, we will cover other functionalities of Tableau. 