_edited_edited.png)
This blog will provide a fundamental understanding of Level of Detail (LOD) expressions in Tableau. We’ll explore LOD expressions using examples from the Superstore dataset to illustrate their applications and benefits.
Introduction: Level of Detail (LOD) expressions in Tableau are powerful tools that enable analysts to control the granularity of their calculations. By specifying the level at which data should be aggregated or analyzed, LOD expressions help manage and interpret complex datasets with multiple dimensions and measures. By using LOD expressions analysts can gain more precise insights and answer specific business questions by defining the level at which calculations should occur.
What is Granularity:
Granularity is a measure of the level of detail in a data structure.
Examples:
Less granular -------------------------------------> More granular
Time Granularity: Year -> Month -> Day -> Hour -> Minute -> Seconds
Geographic Granularity: Country ------> State ------> City ------> Postal Code
Product Granularity: Category -----> Sub Category-----> Product Name
The more dimensions or levels of detail you add, the more granular the data becomes, allowing for deeper and more precise analysis.
Types of LOD expressions:
1.Fixed LOD expressions
2.Include LOD expressions
3.Exclude LOD expressions
1. Fixed LOD expressions: Fixed LOD expressions allow us to calculate a value at a fixed level of granularity and is not dependent or effected by the dimensions present in the view.
Syntax : {FIXED [Dimension]: AGG([Measure])}
Aggregate could be sum, min, max etc.
Example: Let’s take sample Superstore dataset and use category wise sales in fixed LOD.
In the below screenshots we can see category wise total sales and sales after adding one more dimension i.e Segment ( Segment wise sales). Let’s use the Superstore dataset to illustrate 'Fixed Level of Detail' (LOD) expressions with category-wise sales.
In the following screenshots, you'll see:
Category-Wise Total Sales: This view shows total sales aggregated at the category level without considering additional dimensions.
Sales by Segment: By adding the Segment dimension, you can see how sales are distributed across different segments within each category.
But if we want to see both Category wise and Segment wise sales in the same view, we can use 'Fixed LOD expression.' Let’s create a calculated field (Fixed_Category) for fixed category wise sales.
Once we display 'Fixed_Category ' in calculated field, we can see both category wise and segment wise sales in the same view.
Notes:
FIXED level of detail expressions compute values using the specified dimensions without reference to the view level of detail i.e., regardless of other dimensions in the view.
FIXED level of detail expressions ignores all the filters in the view other than context filters, data source filters, and extract filters.
The Tableau order of operations:
2.Include LOD expressions: INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.
Syntax : {INCLUDE [Dimension]: AGG([Measure])}
Example:
Requirement: In addition to Category wise sales we need to show average regional profit too without showing region in the view.
To get the above result without showing region in the view we will create calculated field ('Avg_Regional_Profit') using Include LOD expression. We can use Avg. function either in calculated field or later at the visualization level. In the below screenshot I have included within in the calculated field.
Once we use Include “Avg_Regional_Profit” calculated field, we will be able to meet our requirement to show average regional profit without showing region in the view along with Category wise sales.
Notes:
Include LOD expressions allows you to compute aggregations that are more granular than the view level, including additional dimensions.
Calculation is affected by dimension filters.
The Tableau order of operations:
3. Exclude LOD expressions: Exclude LOD expressions enable the computation of aggregations that are less granular than the view, excluding certain dimensions.
Syntax: {EXCLUDE [Dimension]: AGG([Measure])}
Example:
Requirement: To calculate Category wise sales excluding segment in the view.
We can achieve the above result either with 'Fixed LOD' or 'Exclude LOD' expressions but as we discussed earlier that 'Fixed LOD expressions' are not effected by dimension filter and our requirement is to exclude segment from calculation and be sensitive to further filters.
In order to exclude segment from our calculation we will create new calculated field ( 'Exclude_Segment')
Once we use Include “Exclude_Segment” calculated field, we will be able to meet out requirement to show Category sales along with segment wise sales by excluding segment from it.
Notes:
Use 'Exclude LOD' when you want to remove certain dimensions from your calculation, making it less detailed than the current view
Calculation is affected by dimension filters.
Calculations disregard the excluded dimensions even if filtered.
Conclusion:
Level of Detail (LOD) expressions allow us to compute values at the data source level and the visualization level. We can control the granularity we want to compute with the help of LOD expressions. They can be performed at a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED).
Thanks for reading this blog!
References:
Very well explained!