A Comprehensive Guide to Level of Detail (LOD) Expressions in Tableau

In today’s data-driven world, Tableau has become synonymous with powerful data visualisation and analytics. Whether you’re a seasoned data analyst or a business professional seeking to make sense of complex datasets, Tableau offers a comprehensive set of tools and features to transform raw data into meaningful insights. Businesses today are often confronted with a significant problem – data overload. The abundance of data can lead to a situation where businesses cannot extract valuable insights from their data. Consequently, the inability to process and differentiate poor-quality data from reliable data can have substantial financial implications. 

Tableau has many different capabilities; some of these include the Tableau ranking chart, Tableau Collect, Tableau workbook optimiser, and pivot Tableau. At its heart lies the intuitive Tableau Interface, designed to simplify the process of analysing and visualising data. In this blog post, we will explore the concept of LOD (level of detail) calculations in Tableau.

A Brief Overview of Tableau LOD (Level of Detail)

So, what is LOD in Tableau? Tableau LOD (Level of Detail) functions are powerful tools that allow users to perform complex calculations based on different levels of detail within their data. With LOD functions, users can create calculations that are not limited by the level of aggregation in their view, making it easier to perform calculations that would otherwise be difficult or impossible. By defining the scope of analysis, users can gain deeper insights into their data and answer complex questions that go beyond simple aggregations. 

What are LOD functions?

LOD functions are a set of advanced calculations in Tableau that allow users to perform complex calculations across different levels of detail within their data. They allow users to define the level of detail for a particular calculation, regardless of the level of aggregation in their view. 

Types of LOD Functions

Before learning exactly how to use LOD functions in Tableau, it’s important first to understand the different types of functions. There are three types of LOD functions in Tableau: FIXED, INCLUDE, and EXCLUDE. Each type of LOD function defines a different level of detail for a particular calculation.

1. FIXED LOD Functions: 

These functions allow users to define a fixed level of detail for a particular calculation, regardless of the level of detail in their view. FIXED LOD functions are useful when users need to perform calculations at a specific level of detail, such as at the customer or product level.

For Example:

Calculate the average sales per customer, regardless of the level of detail in the view: {FIXED [Customer ID]: AVG([Sales])}

2. INCLUDE LOD Functions: 

These functions allow users to include additional fields in the calculation while still preserving the level of detail of the view. INCLUDE LOD functions are useful when users need to perform calculations that include additional fields but still need to preserve the level of detail in their view.

For example:

Calculate the maximum sales for each product category while still preserving the level of detail of the view: {INCLUDE [Category]: MAX([Sales])}

3. EXCLUDE LOD Functions:

These functions allow users to exclude specific fields from the calculation while still preserving the level of detail of the view. EXCLUDE LOD functions are useful when users need to perform calculations that exclude specific fields but still need to preserve the level of detail in their view.

For example:

Calculate the total sales, excluding the region field, while still preserving the level of detail of the view: {EXCLUDE [State]: SUM([Sales])}

Order of Operation when Performing LOD in Viz

It is always imperative to remember the order of operation while using LOD, which calculation and filter will run first.

Scenario-based LODs

Now, let’s delve into some real-life Tableau dashboard examples that will bring the concept of LODs to life. By exploring these practical examples, you should be able to gain a deeper understanding of what types of problems Tableau can solve, as well as a greater understanding of how LOD can be applied in different scenarios and contexts. 

Scenario 1

Problem Statement: 

The objective is to create a comprehensive visualisation in Tableau that presents crucial information regarding orders, customers, products, and sales. The primary focus is identifying the products that have garnered the highest monetary investment from customers, along with the corresponding time periods. We aim to gain insights into customer spending behaviours, pinpoint the most financially impactful products, and determine specific timeframes during which these transactions occurred.

Solution:

• First, create a viz on orders, customers, products, and sales as below.

• Now, I want to see only the products which have the maximum sales. To do that, create a LOD calculation. 
• Fixed Max sales: {FIXED [Order ID], [Customer Name]: MAX([Sales])}
• Convert this to dimension and create a new conditional calculation that will filter only products that have max sales.
• Product Max sales by order and customer: {FIXED [Order ID], [Customer Name], [Product Name]: MAX([Sales])} = [Fixed Max sales]
• Put this into the filter and select ‘True’. 
• To check when a customer purchased the product, add the order date to the rows.

Final output:

Scenario 2

Problem Statement:

The objective is to analyse sales and profit data for all states and identify those experiencing unprofitability. The goal is to gain insights into the underlying factors causing the lack of profitability in these states. By addressing this requirement, we aim to understand the root causes of the financial challenges and develop strategies to rectify the situation. Leveraging comprehensive data analysis techniques, we will identify the unprofitable states, examine the contributing factors, and provide actionable insights to drive profitability improvements. 

Solution:

• First, create a viz of sales and profit by states as below.

• Then, create a LOD calculation that found unprofitable states with average discounts.
• Avg discount of unprofitable states: AVG ({FIXED [State]: (IF SUM([Profit]) <0 THEN SUM([Discount]) END)})
• Add the calculation to the size marks card.
• It will only show unprofitable states and their discount. Due to high discounts, profit is negative.

In conclusion, understanding and utilising Level Of Detail (LOD) functions in Tableau can elevate your data analysis and visualisation techniques to new heights. Throughout this blog post, we explored the fundamentals of LOD functions, delving into their significance and applicability in various scenarios. 

LOD functions grant us the power to perform calculations at different levels of granularity, enabling us to answer complex business questions that were once challenging to address. By breaking down data silos and offering more flexibility in our analyses, LOD functions allow us to extract deeper insights and unlock hidden patterns in our datasets. 

We witnessed the versatility of LOD functions through practical examples. From aggregating data with FIXED LOD to filtering data at a different level with INCLUDE and EXCLUDE LOD, these functions proved to be indispensable tools for precise analysis. Furthermore, the interplay between dimensions and measures in combination with LOD expressions showcased the incredible depth of analysis that can be achieved. 

By harnessing the potential of LOD functions, data analysts and business professionals can make data-driven decisions with greater confidence. Whether working with complex data relationships or facing intricate analytical challenges, LOD functions can provide the clarity and precision needed to make sense of it all. 

Further Reading

If you’d like to learn more about various topics around data analytics, be sure to check out our blog posts here!

About Skillfield:

Skillfield is a Melbourne based Cyber Security and Data Services consultancy and professional services company. We provide solutions that help our customers discover, protect and optimise big data in a way that works for them.