Why should I know LOD expressions?

Level Of Detail Expressions allow you to compute calculations at the aggregation level you want. In your worksheets you generally create calculations at the visualization level of detail (by dropping measures or dimension in columns/rows), but we may need to explicitly set the granularity of the calculation in order to show the result we want.

In order to start creating LOD expressions you must first know the granularity of your dataset. So you should ask yourself “What does a single row in my data represents?”. After this you will start understanding the aggregation levels of your data, for example you can have a line per Order ID, but each Order ID can have the same Customer ID, so then the Customer ID’s will be an aggregated level of your data.

How do I create an LOD Expression?

Start by creating a Calculated Field.

LOD expressions are written inside curly brackets. Open the curly brackets and specify your LOD Expression (fixed, include or exclude), then insert the Dimensions you want to use for your calculation, for example “Category” or/and “Customer Name”. You can add as many dimensions as your calculation needs, separated by a comma. Then insert a colon and the Aggregate expression you need (Max, Min, Sum, Avg, etc.) and after it insert the Measure you want to calculate (Profit, Sales, Count of Customer, etc.), finish by closing the curly brackets.

Types of LOD Expressions

There are 3 LOD Expressions in Tableau which are, INCLUDE, EXCLUDE and FIXED.

EXCLUDE

EXCLUDE level of detail expressions prevent the calculation from using one or more of the dimensions present in the view.

In the next Chart I calculated the difference of the AVG Sales of each Country vs their continent by Month.

The calculation returns the Avg Sales Excluding the Countries, and then deduct the Avg Sales of my visualization view. As I am Excluding the countries in my LOD calculation It is returning me a value at the most aggregated level of my data. But then I wanted the difference to their respective Continent, so I added Continent in my view to stop the Exclude calculation at the Continent level.

Include

INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.
INCLUDE 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 coarser level of detail in your view.

In the next chart I’m calculating for each Customer ID the Distinct Count of Order ID’s, and then showing it at my visualization level which is the Countries. So, Canada has 4.85 avg distinct orders per Customer ID.

FIXED

FIXED level of detail expressions computes a value using the specified dimensions, without reference to the dimensions in the view. In other words, with the FIXED expression you can create a calculation despite the Dimension in your visualization; as opposed to Exclude and Include, where your visualization affects the result shown. Fixed expression are the ones you will usually use the most as the result is independent of the dimensions in your view.

In this case I wanted to calculate which is the Continent with the highest amount of Customer who ordered twice.

In order to create that calculation, I needed to get the Distinct Count of Order Id’s per Customer Name and also per Continent. As I needed to fix these 2 Dimensions I entered both separated by a comma. The result will go to your measures but we need to make it a Discrete Dimension. To do this you can right click on it in your measures list and choose “Convert to Dimension”.

The chart finally shows that North America is the continent with the highest number of customers who ordered twice. 2785 Customer ordered twice in North America.