Tableau - LOD Expressions

 

Before we dive into LOD expressions, namely, FIXED, INCLUDE and EXCLUDE, let's take a look at data source level of detail and view level of detail.  To make it clear, data source refers to the dataset appearing on Data pane on the left side of Tableau window, rather than the one in a database. It may be processed through Tableau Prep or Physical layer on the data model. Be noted, relationship in the new data model combines data from more than one tables in a dynamic and flexible way. To demonstrate the concept of LOD expressions in an easier manner, we will not discuss relationship here.

Simply put, data source has the finest granularity which can only be rolled up in the visualization. Data in a view is summarized by dimensions integrated into the view. As shown on the picture below, any dimension dropped into the red boxes will affect the granularity of data used in the view. They all together consist of the view level of detail. (Noted the super store dataset is employed for the demonstration.)

• Country
• Month Of Order Date
• Product Sub Category
• Ship Mode

Figure 1: View Level of Detail

You can right click on the view and select View Data menu to check the data set behind, as shown below. The measure value is summed up by the above dimensions.

Figure 2: Underlying Data

On the other hand, the original data source contains more dimensions, for example, City, Customer Segment, Product Category etc., as shown on the following figure. 

After we built a concept of data source level of detail and view level of detail, it becomes easier to understand LOD expressions. In nature they bring a summarized measure value with a different LOD into the view. 

FIXED

The expression is to calculate the measure by the dimensions designated in the formula. The calculation will not be affected by the view level of detail.

EXCLUDE

EXCLUDE expression will take the specified dimension away from the view level of detail while calculating the measure. 

Let's take a look at the example below. The view shows the quarterly sales for each product category. The view LOD is defined by Order Date (Quarterly) and Product Category. In case that we want to present the total sum of all product categories at the same time, let's see how EXCLUDE expression can help us achieve this.

Drag and drop Sales to Detail box in Marks card.

Replace the aggregation function with the following expression.

{EXCLUDE [Product Category] : SUM([Sales])}

Change it from Measure to Attribute using the context menu.

Add it to Tooltips as shown below.

As expected, we get the total amount of sales per quarter..

One note, EXCLUDE expression is not suitable for row-level calculations, as Tableau Desktop Help states.

"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)." - Create Level of Detail Expressions in Tableau Section, Tableau Desktop and Web Authoring Help

INCLUDE

INCLUDE expression will do the calculation by integrating the specified dimensions into the view level of detail. So it will further down the granularity of data in the view.

The below bar graph shows the total sales per quarter. If we would like to display the average sales per category on this chart, INCLUDE expression is a good option for this. On the second thought, I wouldn't see much business meaning to do so, considering the point is to demonstrate how INCLUDE expression works, so let's start with building the formula.

Create a calculated field named by SalesPerCategory as below.

SalesPerCategory = {INCLUDE [Product Category] : SUM([Sales])}

Drag and Drop SalesPerCategory on Label box.

Change the aggregation type to average. You'll see the category averages on top of each bar.