Level of Detail (LOD) expressions allow you to control the granularity at which calculations are performed. In Tableau, the level of detail in a visualisation is typically defined by the dimensions included. As a result, Level of Detail Calculations are useful when you need to aggregate data at a different level than what is shown in the view as they allow you to go beyond this limitation by performing calculations at a custom level of detail.
There are three main types of LoD Calculations
- Fixed LoD
- Exclude LoD
- Include LoD
Fixed Level LoD’s
Fixed LOD expressions let you specify a set level of detail for your calculation, independent of the dimensions in your visualisation. This allows you to focus on specific dimensions and perform calculations strictly at that defined level.
Fixed LOD’s have to be structured as the example below lays out.
{ FIXED [Dimension] : Aggregation([Measure]) }
For example if you wanted to show each order’s sales but also display the total sales per customer you could use a fixed LOD calc such as the one below.
{ FIXED [Customer ID] : SUM([Sales]) }
This calculates total sales per customer, even if the view contains different dimensions (e.g., Category, Region). It’s important to note that Fixed LOD’s will disregard any filters used on the page unless they are added to context.
Exclude LoD’s
Exclude LoD’s are used to aggregate measures while excluding specific dimensions, thus making it a higher level of granularity calculation. These can be useful when you need to incorporate more detailed levels of analysis within a section of visualisation which is analysing an issue more broadly.
Exclude LoD’s are structured very similarly to Fixed LoD’s. However, instead of the calculation being based only on a specified dimension(s), the calculation removes the specified dimension but considers all others in the view. It is also important to note that Exclude LoD’s are affected by row-level filters.
Exclude LOD’s have to be structured as the example below lays out.
{ EXCLUDE [Dimension] : Aggregation([Measure]) }
For example if you wanted to remove Category in an analysis of sales in order to view total sales by region, while also being able to display the total sales per customer you could use a fixed LOD calc such as the one below.
{ EXCLUDE [Category] : SUM([Sales]) }
This would increase the granularity, enabling you to return total sales by region while also being able to display the total sales per customer alongside it.
Include LoDs
INCLUDE LOD expressions allow you to perform calculations at a more granular level than the visualisation, incorporating additional dimensions without overriding the existing ones. This makes them useful when you want to maintain flexibility while adding more detail to an aggregation.
Include LoDs have to be structured as displayed below
{ INCLUDE [Dimension] : Aggregation([Measure]) }
For example, if you wanted to calculate the average sales per state in visualisation that is at the city level you could use an Include LoD, making them ideal for calculations that should adapt to different levels of aggregation.
{ INCLUDE [State] : AVG([Sales]) }
This would calculate the average sales per state, even if the visualisation is at the city level, while being able to dynamically change depending on the dimensions in the view. This ensures state-level insights do not disrupt an already existing city-level visualisation.