LoD Expressions in Tableau

What are the LoD Expressions in Tableau?

LoD or Level of Details Expressions is a feature in Tableau that allows you to calculate values at data source and data visualization levels. Using these expressions, you can define the granularity at which you want to perform your calculation for visualizations, i.e., the flexibility of data aggregation at different levels of granularity. LoD expressions are highly useful as they provide you with greater control over the data analysis you want to perform on your dataset.

A sample LoD expression used in Tableau visualization is provided below. Understanding LOD expressions in Tableau is critical to creating a more insightful analysis for your dataset that can be used by business stakeholders.

Types

Tableau supports three different types of LoD expressions, and each of these expressions has its syntax and usabilities as provided below:

#1 – FIXED LoD

• Definition:
  • FIXED Level of Details allows you to calculate values using the dimensions you have specified. 
  • It doesn’t refer to any dimensions in the Tableau view, which means regardless of what dimensions you include in the Tableau view, your calculated values remain the same.
• Syntax:
  • To use FIXED LoD, you can use the syntax highlighted below

{FIXED [State]: SUM([Revenue])}

The above expression will compute the total revenue per state in Tableau regardless of whether you add additional dimensions such as Product or Category to the view.

• Usability:
  • FIXED LoD can be useful when you want to calculate the measure at a fixed level regardless of the dimensions in the view.

#2 – INCLUDE LoD

• Definition:
  • INCLUDE Level of Details allows you to calculate values using the dimensions you have specified in addition to the specified dimensions in the Tableau view.
• Syntax:
  • To use INCLUDE LoD, you can use the following syntax in Tableau

{ INCLUDE [Product Id] : SUM([Revenue]) }

The above expression will compute the total revenue per Product in Tableau.

• Usability:
  • INCLUDE can be useful when you want to compute the measure at a granular detail but further aggregate it according to the fields included in the Tableau view.
  • Depending on the fields you add or delete from the view, dimensions based on INCLUDE LoD expression changes.

#3 – EXCLUDE LoD

• Definition:
  • EXCLUDE Level of Details allows you to calculate values excluding the dimension specified in the view.
• Syntax:
  • To use EXCLUDE LoD, you can use the below syntax in Tableau.

{EXCLUDE [Category]: SUM([Profit])}

The above expression will exclude Category from the Sum of Profit in the Tableau view.

• Usability:
  • EXCLUDE LoD can be useful for scenarios where you want to calculate % of total or deviation from the median or mean value, etc.

In the next section, let’s look at how to use LoD expressions in Tableau.

How to use Level of Detail (LoD) Expressions in Tableau?

To use Level of Detail (LoD) expressions in Tableau, you may follow the instructions outlined below: 

Create Data Visualization

Step 1: Launch Tableau Public on your machine and Import your data source into Tableau Public.

Step 2: From the Data pane, drag the Type dimension to the Columns shelf and Gross Income to the Rows shelf in the Tableau view. Switch the view to the Entire view.

Tableau will create a bar chart visualization in the view.

Configure the LoD expression

Step 3: Click on the down arrow next to the search text box and choose Create Calculated Field…

Step 4: In the calculation editor, specify the field name and enter the INCLUDE LoD expression to provide the logic. 

Here, we specified the field name as Gross Income Per Cust Type.

Click on OK to save the changes.

Tableau will create the LoD expression in the Data pane, as shown below.

Use the LoD expression in Tableau View

Step 5: Drag the Gross Income Per Cust Type to the Rows shelf and place it to the left of SUM(Gross Income).

Step 6: On the Rows shelf, click on the down arrow next to SUM(Gross Income Per Cust Type) and select Measure(Sum) – Average.

Tableau will display Gross Income for Type and Average Gross Income Per Cust Type per Type in the view.

In the next section, we will explore different scenarios where you can use LoD expressions in Tableau view.

Examples

In this section, we will demonstrate different scenarios that you can reference when creating and using the Level of Detail (LoD) expressions in Tableau.

Example #1 – INCLUDE LOD Expressions in Tableau

In this example, we have demonstrated how to use INCLUDE LOD expressions in Tableau using the Trent online Store Sales dataset. The Trent Online Store Sales dataset contains product sales details such as Segment, Country, Product, as well as sales metrics capturing the COGS, Sales, Profit, Units sold, etc.

To use the INCLUDE LOD expressions in Tableau, follow the instructions outlined below:

Step 1: Connect to Trent online Store Sales dataset from Tableau Public using the File Navigator. 

Step 2: Drag Profit to the Columns shelf and Year and Product fields to the Rows shelf in the Tableau view. Tableau will create a bar chart in the view as shown below.

Step 3: In the right toolbar, under Click Me, choose the text tables option.

Tableau will update the view with a text table, as shown below.

Step 4: Select Segment and Profit and then control-click on the Segment field.

Select the Create – LOD Calculation option from the context menu to create a quick LOD calculation.

Step 5: In the calculation editor window, specify the name and create a LOD expression using the INCLUDE keyword.

Step 6: Right-click on the ProfitbySegment field on the Columns shelf and change the SUM to Average.

Step 7: Drag the Product to Color, SUM(ProfitbySegment) to the Label on the Marks card. Tableau will add color coding to the view. As you can see, the visual displays the Average profit by product segment across different years and product combinations.

Example #2 – EXCLUDE LOD Expressions in Tableau

In this example, we will demonstrate using EXCLUDE LOD expressions in Tableau. For this demo, we have used the Customer360Insights dataset that contains a synthetic collection meticulously designed to mirror the multifaceted nature of customer interactions within an e-commerce platform, such as Customer Demographics, Geographical Data, Product Information, Transactional data, etc.

To use EXCLUDE LOD expressions in Tableau, follow the instructions outlined below:

Step 1: Import Customer360Insights data into the Tableau interface using File – Open functionality.

Step 2: Create a calculated field Revenue by specifying the logic in the Calculation editor window.

Step 3: Drag the Revenue measure to the Columns shelf and Category to the Rows shelf to create a bar chart visualization in Tableau view.

Step 4: Now drag the Product dimension to the Rows shelf to update the visualization.

Step 5: Create a calculated field ExcludebyProduct by using the EXCLUDE LOD expression in the Tableau calculation editor screen. Click on OK to save the changes.

Step 6: Add the LOD expression to the Label on the Marks card. Swap the Rows and Columns.

Step 7: Drag the Revenue field to the Color on the Marks card. Click on Color – Edit Colors. In the Edit Colors screen, choose the color palette option.

Tableau will add color coding to the view and sort it based on Revenue for each combination of category and product.

Step 8: Now remove the Revenue from Color and add ExcludebyProduct to it. You will see that Tableau has sorted the view by considering only the category and not the product field.

Example #3 – FIXED LOD Expressions in Tableau

In this example, we will demonstrate how to create and use FIXED LOD expressions using Brand Sales Adspend data in Tableau. Brand Sales Adspend data contains details of sales performance and advertising expenditures of various brands across multiple countries.

To create a FIXED LOD expression and use it in Tableau, follow the instructions outlined below:

Step 1: Import the Brand Sales Adspend data into Tableau public.

Step 2: Drag the Total Ad Spend measure to the Columns shelf and Country dimension to the Rows shelf.

Step 3: Drag the Country field to the Color in the Marks card.

Step 4: Add a label to the view by dragging Total Ad Spend to the Label on the Marks card.

Step 5: Click on the down arrow next to the Search box and select Create Calculated Field.

Step 6: Specify the field name and FIXED LOD expression in the calculation editor screen.

Step 7: Drag the fields to the view to create your visualization. You can see that while the Total Ad Spend measure takes into account Brand Name and Country to show the aggregated value, Ad Spend by Brand Name aggregates the value at the Brand Name level.

Importance

Level of Detail (LoD) expressions are essential features in Tableau due to the following:

• Ability to control data aggregation
  • Using the Tableau LoD expression, you can control the granularity where you can use
    • INCLUDE – for more data granularity
    • EXCLUDE – for less data granularity
    • FIXED – for fixed or isolated data granularity
• Flexibility to perform complex calculations
  • You can perform complex data aggregations which otherwise wouldn’t be possible through conventional approaches
• Enhancement to data visualization
  • You can use the LoD expressions in Tableau visualizations along with other dimensions and measures

Important Things to Note

• While LoD expressions are helpful features, you must use them carefully to achieve accurate results in your calculations.
• Tableau provides quick LoD calculation functionality, so you can create LoD expressions without manually entering the calculation logic in the calculation editor.
• If you are using nested LoD expressions in Tableau, ensure that you have validated the results thoroughly to avoid any unintended results.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand LoD Expressions in Tableau with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is LoD Expressions in Tableau. We learn how to use Level of Detail (LoD) Expressions and its types along with examples and importance. You can learn more from the following articles –