LOD expressions = Level of Detail Expressions
They allow you to compute values at a different level of granularity than the visualisation itself. Fixed LODs are a very powerful and commonly used type of LOD.
This blog will explore:
- What LODs are
- The different types of LODs
- How fixed LODs work
- When to use the different types of LODs
- Real world examples of LODs in action
What are LODs?
LODs are used in calculations to define the granularity of that particular calculation, independent of the data in the view. They allow you to override the granularity defined by the dimensions in the view.
There are 3 main types of LODs:
- Fixed
- Include
- Exclude
Why would you use LODs?
For example, in sales data analysis, you may want to:
- Find average sales per region when viewing city-level data
- Compare individual sales to regional averages
- Temporarily ignore dimensions in the view
Understanding Fixed LODs
A fixed LOD computes a value using only the dimension you have specified directly, regardless of other dimensions.
Syntax:
{Fixed [Dimension] : Agg ([Measure])}
Example:
{Fixed [Customer Name] : SUM([Sales])}
This will calculate total sales per customer, regardless of what is in the visualisation. It calculates a metric at exactly the level specified, allowing you to ignore dimensions that are not of interest for a specific calculation.
Example 1: Total Sales Per Customer
E.g., your visualisation currently shows:
- Region
- Product Category
- Sales
But, you may also want to see each customer's lifetime sales. So, you would fix sales to customer name.
{Fixed [Customer Name] : Sum([Sales])}
Now, even if the chart changes, the calculation will still return the customer's total sales across the whole dataset.
Example 2: Average Order Value Per Customer
Let's take a look at using LODs in practice with other calculations.
If each customer has multiple orders, you may want to see their average order value.
Step 1 - Customer Sales:
{ FIXED [Customer Name] : SUM([Sales]) }
Step 2 - Customer Orders:
{ FIXED [Customer Name] : COUNTD([Order ID]) }
Step 3 - Average Order Value:
[Customer Sales] / [Customer Orders]
The Catch: Fixed LODs and Filters
Fixed LODs are computed AFTER context filters, but BEFORE dimension filters.
So normal filters may not affect your fixed LOD calculation.
For example, if you do a fixed LOD on region and Sum (Sales), but then filter the view to only show furniture, the LOD calculation may still include all categories unless the filter is added to context.
Include LODs
An Include LOD allows you to add another dimension to the current view level.
{Include [Dimension] : AGG ([Measure])}
For example, if your visualisation currently shows region and sales, but you want to see average sales per customer within each region, you would use an Include LOD:
{Include [Customer Name] : Sum([Sales])}
You would then place the average of this field in your visualisation.
So, tableau first calculates sales per customer, then averages those customer totals within each region.
Exclude LODs
An Exclude LOD removes dimensions from the current view during calculation.
{ EXCLUDE [Dimension] : AGG([Measure]) }
For example, if your visualisation contains region, state and sales, but you want to see total regional sales repeated across all states, you would use an Exclude LOD:
{ EXCLUDE [State] : SUM([Sales]) }
This calculation now ignores state and returns the total sales for that region, repeated across all states.
Final Thoughts
Some common mistakes with LODs are:
- Forgetting to add an aggregation. LODs will not work unless the measure is aggregated to a given level.
- Ignoring filter order. Fixed calculations may ignore regular filters unless filters are context filters, or filters are data source filters.
LOD expressions are a very powerful feature of tableau because they allow you to control calculation granularity independently from the visualisation.