Blog

You are currently viewing Advanced Calculations with Level of Details- LOD Expressions

Advanced Calculations with Level of Details- LOD Expressions

Loading

In this blog, we will be going to talk about LOD expressions or Level of Details expression in Tableau allow users to perform calculations at different data granularities or levels, beyond what’s available in a standard aggregation or filter. They enable detailed and flexible analysis by fixing, including, or excluding specific dimensions in calculations. LOD expressions are extremely powerful for performing advanced calculations that aggregate data in ways not inherently possible within the default framework of Tableau’s visualization tools.

Three main types of LOD expressions

  1. Fixed
  2. Include
  3. Exclude

When to Use LOD Expressions

Handling Multi-level Data Aggregations

When you need to calculate measures at different levels of aggregation within the same view (e.g., comparing city-level sales to country-level sales).

Comparing Aggregate Measures

When performing comparisons like average sales by customer or showing both total and regional sales in a single view.

Working Around Filters

LOD expressions allow you to compute values that are unaffected by the filters applied to a view, ensuring your calculation considers the entire dataset, or ignores specific dimensions as needed.

Cohort Analysis and Customer Segmentation

LODs can easily segment data for deep analysis of customer behaviour, especially when looking at subsets of data such as customer churn rates.

Benefits of Using LOD Expressions

Granular Control Over Data

LOD expressions allow users to control the granularity of the data being analyzed, making it possible to calculate results at different levels of detail than what is being displayed in the view.

Flexibility in Complex Calculations

They provide the flexibility to calculate complex metrics without having to manipulate the dataset or rely on intricate data models.

Consistency in Calculations

LODs allow for consistent calculations across different views by controlling how dimensions are factored into aggregations, giving users more predictable outcomes.

Advanced Data Exploration

LOD expressions make it possible to perform deeper analyses, such as ranking within specific dimensions, calculating retention rates, or determining average order sizes across different product lines.

Uses of LOD Expressions in Tableau

Average Sales per Customer

LOD expressions can calculate average sales per customer, irrespective of filters applied in a view. This is crucial when analyzing customer behavior over time or by region.

Comparing City vs. Country Sales

Use LOD to aggregate sales at both the city and country levels within the same chart, allowing users to see the broader context while also drilling down into specific locations.

Customer Retention Analysis

Calculate customer retention rates by fixing the calculation at the customer ID level to track repeat purchases across different time periods.

Rank Calculation

LOD expressions help rank products or salespeople across multiple dimensions, like products sold by a salesperson within each region.

Steps to create Heat Maps and Density Plots

Step 1: Open Tableau

Step 2: Click on Text file to connect with Tableau

Step 3: Browse csv file (or other file format as your need) and click on Open

Step 4: Data has been loaded, now click on Sheet 1

FIXED

Step 5: Drag-n-drop Salesperson ID and Customer Name in Rows

Step 6: Drag Measure Names and drop in Filter shelf

Step 7: Select only Sales Amount

Step 8: Drag Measure Names (under Filter shelf) and drop over Text (Abc)

Output with SUM: Take a look on data

Step 10: Click on Analysis tab and select Create Calculated Field…

Step 11: Specify a name (Fixed with Customer) for field and fill the following:

Step 12: Drag Fixed with Customer field from data pane and drop in Measure Values shelf

Note:

  • The fixed LOD expression provides a static, customer-level total (1,400.0) for Emma Johnson in the view.
  • Whether, Sales Amount changes based on the salesperson ID, showing the specific sales for each salesperson’s transaction with Emma Johnson.

INCLUDE

Step 13: Drag-n-Drop Country to Columns and Sales Amount to Rows

Step 14: Click on Label (marks card) and mark on Show mark labels

Step 15: Right click on Sum of Sales Amount and select Measure(Sum) for selecting Average

Step 16: Click on Analysis tab and select Create Calculated Field…

Step 17: Specify a name (Include with Country) to field and fill the following:

Step 18: Drag Include with Country field from data pane and drop in Rows

Step 19: Right click on sum of Include with Country (Rows) and select Measure(Sum) for selecting Average

Output:

Note:

  • Average Sales Amount: This averages the sales amount based on the current level of detail in the view, which in this case is the country level.
  • Include Average with Country: This measure, due to INCLUDE LOD, takes into account any additional details (such as customers or products) at the country level, showing a broader scope of the data within each country.

EXCLUDE

Step 21: Drag-n-Drop Salesperson ID and Customer Name and drop in Rows

Step 22: Drag Measure Names (Data pane) and drop in Filters shelf

Step 23: Mark on Sales Amount only and hit OK

Step 24: Drag Measure Names: Sales Amount (Filters shelf) and drop over Text (Abc)

Output with Sum: Take a look on data

Step 26: Click on Analysis tab and select Create Calculated Field…

Step 27: Specify a name (Exclude with Customer) to field and fill the following:

Step 28: Drag Exclude with Customer field (Data pane) and drop in Measure Values shelf

Note:

  • SUM(Sales Amount) shows the actual sales amount per customer name.
  • SUM with Customer Excluded shows the sum of the sales amount for each Salesperson ID, excluding the customer name in its calculation, thereby showing a uniform sales amount across each Salesperson regardless of specific customers.

Conclusion

Level of Detail (LOD) expressions in Tableau are indispensable for performing advanced calculations that require a finer level of control over data aggregation. Whether you need to fix calculations at a specific granularity, include additional dimensions, or exclude certain fields, LOD expressions enable powerful, flexible analysis. They are particularly useful when you need to perform complex calculations like average sales per customer, rank products by region, or compare aggregate measures across different dimensions.

By mastering LOD expressions, users can gain deeper insights into their data and create more dynamic, multi-dimensional visualizations. These capabilities make Tableau a more versatile tool for tackling complex business questions and unlocking the full potential of data-driven analysis.

If you like the article and would like to support me, make sure to:

Leave a Reply