Level of Detail (LOD) Expressions in Tableau

Level of Detail (LOD) expressions in Tableau allow you to compute values at differing levels of granularity/detail.

These expressions can allow you to fine-tune your computations at lesser, greater or independent levels of granularity. This grants the user greater flexibility when analysing data. To utilise and appreciate LOD expressions in Tableau, you need to first understand the order of operations. This order dictates in what order Tableau performs various actions.

 

 

Tableau’s Order of Operations

 

LOD Expressions interact with Tableau’s Order of Operations, which dictate how data is affected depending on the order of action used.  For instance, adding a simple table calculation at the lowest order would be affected by a dimensional filter. The order dictates that in this example, data is filtered before table calculations are made.

 

 

 

LOD expressions allow users to bypass the order and more readily control the granularity of computations. Let’s begin with a Fixed LOD, the most common LOD expression in Tableau.

 

 

Fixed LODs

 

Fixed LODs are the most commonly used LOD in Tableau as they calculate aggregation independent of any filter in Tableau’s sheet view (excluding context filters). Using Tableau’s Superstore – EU data, I’ve created two Fixed LODs to calculate the Total % Contribution of each Sub-Category to overall Sales. This will allow us to visualise a constant % Contribution regardless of dimensional filters (eg. for Category).

The first LOD created simply calculated the overall sum of sales as a ‘Grand Total Sales’ field:

 

{ FIXED : sum([Sales]) }

 

This calculation is made at the level specified in the Order of Operations, and so is not affected by Tableau’s window level. You can specify a variable to aggregate at after ‘FIXED’ before the semi-colon, however as we need an overall value for this calculation, we can leave it blank.

Next, we can use ‘Grand Total Sales’ as the denominator for our final Fixed LOD – the % Contribution to Sales at the Sub-Category level:

 

( { FIXED [Sub-Category]: sum([Sales])} / [Grand Total Sales] )

 

This takes the sum of sales for each Sub-Category and divides it by the total sales. All that’s left to do is format the field as a percentage.

Now when filtering by Category, the % Contribution remains the same for each Sub-Category. We can see below that removing the Category ‘Furniture’ from the view has no effect on the % Contribution column. This calculation would have changed had I used a Table Calculation alternative.

 

 

 

 

Include LODs

 

Include LODs exist to allow users to compute at a finer level of granularity, including specified variables that may not exist in the sheet view. These expressions come in handy when calculating to a finer level, before re-aggregating to show broader context. In this next example using Superstore – EU data, I’ll show how the Include LOD can calculate the average sales per product of each Sub-Category.

As each LOD Expression follows the same format, all I’m doing here is using the INCLUDE expression to calculate the average Sales per Product Name:

 

{ INCLUDE [Product Name]: avg([Sales])}

 

When bringing this new field onto the Columns, we can see that the ‘Tables’ Sub-Category contains the products with the highest average sales.

 

 

 

 

 

Exclude LODs

 

Exclude LODs calculate at a coarser level, preventing a calculation from using specified dimension/s in the sheet view. Use cases for this expression include calculating a percent of the total, the difference from an overall average, or viewing a coarser average for a finer LOD. For this last example, I’ll be using the Superstore – EU sample dataset.

Calculating an average discount per Sub-Category, I’d like to see how these sub-categories compare to the overall Category average by having both calculations in view. Let’s create our calculated field based on an EXCLUDE expression:

 

{ EXCLUDE [Sub-Category]: avg([Discount])}

 

With all LOD Expressions carrying the same format, this should be pretty easy to read by now. We want to calculate the average discount for the current view, excluding the Sub-Category field, therefore averaging at the Category level. I’ll create a quick Boolean field to colour Sub-Categories under or over the Category average. We can see what this looks like below.

 

 

 

Level of Detail (LOD) Expressions in Tableau provide greater flexibility when analysing data. Hopefully with this quick rundown you’ll have learnt a thing or two about how to utilise these expressions!