_edited_edited.png)
In today’s fast-paced, data-driven world, deriving actionable insights from complex datasets is critical for businesses. Tableau, one of the leading tools for data visualization, equips analysts with advanced features to manipulate, aggregate, and refine data to uncover deep insights. Among these features, Level of Detail (LOD) expressions play a pivotal role in controlling how data is aggregated and analyzed.
In this guide, we will focus on EXCLUDE LOD expressions—what they are, how they work, and how they can help you gain precise control over data aggregation.
What Are LOD Expressions?
In Tableau, LOD expressions enable users to perform calculations at a more granular or aggregated level than what is displayed in the view. LOD expressions can override the default aggregation Tableau applies, giving more control over the calculations.
There are three main types of LOD expressions:
FIXED LOD: Aggregates data at a specific level of detail, ignoring the filters or other dimensions in the view.
INCLUDE LOD: Aggregates data by including additional dimensions not currently present in the visualization.
EXCLUDE LOD: Performs calculations while excluding specific dimensions, even if they are displayed in the view.
Here, we will concentrate on EXCLUDE LOD expressions, explaining their purpose, use cases, and how they offer deeper analytical precision.
Exploring EXCLUDE LOD: Simplifying Aggregation by Removing Dimensions
While both FIXED and INCLUDE LOD expressions focus on adding or fixing dimensions, EXCLUDE LOD removes dimensions from the aggregation process. This gives you control over what dimensions should be excluded from the calculation, providing a simplified view of the data.
For example, if you want to calculate a region's total sales without accounting for individual sub-categories, you can exclude the "Sub-Category" dimension from the calculation using EXCLUDE LOD.
However, EXCLUDE LOD is not about just hiding dimensions from the visualization. It is used when the excluded dimension impacts the calculation, and removing it helps in calculating metrics at a higher level of aggregation.
When to Use EXCLUDE LOD: Simplifying Aggregations
EXCLUDE LOD is particularly useful when you want to remove dimensions from a calculation while keeping those dimensions in the view. This allows for higher-level summaries of data, helping you identify trends across broader categories without being distracted by detailed breakdowns.
For instance, you might want to display overall sales for a region without considering individual products within that region. In such cases, EXCLUDE LOD expressions allow you to remove the product dimension from your calculations while still showing region-wise sales totals.
Example Scenario: Region-Wise Total Sales Without Sub-Category Breakdown
Let’s assume you have sales data (rows) that is segmented by Region (columns) and Sub-Category (columns), and you want to calculate the total sales per region, without considering the breakdown by sub-category. When you visually segment the data by both Region and Sub-Category, Tableau will show total sales for each sub-category within each region as show below.
However, this might clutter your view if you only want to see the total for each region. To achieve this, you can use an EXCLUDE LOD expression to exclude the Sub-Category dimension from the calculation while keeping it in the view.
Steps to Implement EXCLUDE LOD:
Create a Calculated Field:
Open Tableau and connect to your data source.
Go to the Data pane on the left.
Right-click and select Create Calculated Field from the dropdown menu.
Input the LOD Expression:
Name your calculated field (e.g., "Total Sales by Region (Exclude Sub-Category)").
Enter the EXCLUDE LOD expression:{ EXCLUDE [Sub-Category] : SUM([Sales]) }
Click OK to save the calculated field.
Incorporate the EXCLUDE LOD Field into Your Visualization:
Drag the newly created calculated field to the Rows (or Columns) shelf.
Ensure the measure displays the correct aggregation (in this case, SUM) , Change the chart type as needed from Marks section.
Understanding the Results
By using an EXCLUDE LOD expression, you remove the Sub-Category dimension from the aggregation, but it can still remain visible in the view. This means you can display region-wise total sales, regardless of the individual sub-categories , simplifying your view while maintaining important dimensional context.
If you want to show both total sales per region (blue line) and sales per sub-category (multicolor bars) side by side for comparison, you can use a dual-axis chart:
Dual Axis Chart: Right-click on the second Y-axis (created by your calculated field) and select Dual Axis.
Synchronize Axes: Right-click on one of the axes and choose Synchronize Axis to align the scales, creating a cleaner and more interpretable chart.
Adding a New Dimension: How Category Affects the View While Excluding Sub-Category
By adding Category to the Columns shelf, Tableau adjusts the visualization to show how the total sales (excluding Sub-Category) are distributed across different Categories. Here’s what happens:
Category Breakdown: The view now breaks down the total sales by Category. Each bar or data point reflects the total sales for each category, aggregated at the Region level.
Sub-Category Exclusion: Despite adding Category to the view, the Sub-Category is still excluded from the aggregation due to the EXCLUDE LOD expression. The sum of sales remains calculated at the region level, with Sub-Category not influencing the results.
Dimension Interaction: The addition of Category shows how the total sales (excluding sub-category details) vary across different categories. This helps in understanding the distribution of sales across categories without the granularity of sub-categories affecting the results.
Conclusion: Streamlining Analysis with EXCLUDE LOD
While Tableau allows you to add dimensions and break down data visually using the Marks shelf, EXCLUDE LOD expressions offer precise control over aggregations by removing dimensions that are not relevant to a specific calculation. This makes them ideal for creating simplified metrics while maintaining detailed dimensions in the visualization.
Mastering EXCLUDE LOD allows data analysts to streamline their analyses, removing unnecessary details to focus on higher-level insights. As datasets become more complex, the ability to customize calculations using LOD expressions will be key to uncovering trends and patterns efficiently.
By understanding how and when to apply EXCLUDE LOD, analysts can add greater depth and simplicity to their data visualizations in Tableau.
Comments