Hello everyone!

It has been a couple of weeks since my journey with The Data School began. Recently, we concluded our first project week, and it has been an incredible experience! Our team was tasked with creating a dashboard in Tableau, the core challenge of which was to develop a variety of Level of Detail (LOD) expressions and drill-down capabilities. In this blog post, I will delve into the significance of the order of operations and discuss the three types of LODs in detail.

Tableau’s Order of Operations

Tableau’s Order of Operations is essentially a set of guidelines that dictate how various actions, especially filters, are applied to data. This hierarchy of operations controls the sequence in which data is processed. Understanding this order is crucial for achieving accurate and expected results in data visualization.

In Tableau, Fixed LOD expressions are processed before dimension filters. This ensures that the calculations remain consistent, regardless of any filtering actions applied to the view. On the other hand, Include and Exclude LOD expressions are calculated after dimension filters. This sequencing allows for more refined data manipulation. Specifically, Include LODs enhance the data’s granularity by incorporating dimensions not present in the initial view. Exclude LODs, in contrast, streamline the view by excluding certain dimensions from the calculation, aiding in the generation of broader summary metrics or comparative analyses.

 

Understanding LOD Expressions

LOD (Level of Detail) expressions in Tableau empower users to precisely control the aggregation level of data, independent of the granularity of the visualization. This functionality is essential for executing aggregations that diverge from the level of detail presented in the current view. Tableau offers three types of LOD expressions: Fixed, Include, and Exclude, each serving a distinct purpose in data analysis. Let’s explore each type in detail.

Fixed, Include, Exclude LOD

  1. Fixed LOD

Fixed Level of Detail (LOD) expressions in Tableau perform data aggregation independently of most filters used in the sheet view. This feature is invaluable for generating consistent values across various dimensions. Consider, for example, calculating the total percentage that each sub-category contributes to overall sales. Notably, these percentages remain constant, unaffected by the application of filters such as categories.

Step-by-Step Guide

1. Start by ensuring you are connected to the Superstore dataset in Tableau.
2. Drag the fields ‘Category’ and ‘Sub-Category’ onto the Rows shelf within your Tableau workspace.
3. Add the ‘Sales’ data by dragging the ‘Sales’ field onto your table.
4. Create a Fixed Level of Detail (LOD) calculation to aggregate sales by category. This is essential for analysing sales data for each category, independent of other dimensions.
{FIXED [Category] : SUM([Sales])}
4.1 Incorporate this new calculated field into your table or view to enable comparison and analysis of the fixed sales sum by category.
5. Create another calculated field to calculate the percentage of sales by category. This should be the sum of sales divided by the previously calculated Fixed LOD sum of sales.
SUM([Sales])/ SUM([LOD Fixed Sum of Sales)]
5.2 Add this second calculated field to your table or view, and format it as a percentage with one decimal place.

  1. Include LOD

Include Level of Detail (LOD) expressions facilitate calculations at a more granular level, even incorporating variables not displayed in the current sheet view. These expressions are particularly useful for performing detailed calculations before aggregating the data to a higher level. For example, calculating the average sales per product within each sub-category can yield valuable insights into the performance of products in those categories.

Step-by-Step Guide

Begin by creating a new sheet using the Superstore data set.

  1. Add the ‘Sub-Category’ field to the Columns shelf in your Tableau workspace.
  2. Develop an Include LOD calculation to determine the average sales at the product level. {INCLUDE [Product Name]: AVG([Sales])}
  3. Introduce the ‘Include LOD Av. Sales’ calculated field, created in Step 2, by dragging it onto the rows shelf.

 

  1. Exclude LOD

Exclude Level of Detail (LOD) expressions operate by omitting specific dimensions from calculations within the sheet view. These expressions are beneficial for numerous operations. In this example, we will use them to compute the sales difference between a specific sub-category, such as ‘Paper,’ and other sub-categories.

Step-by-Step Guide

Start by creating a new sheet using the Superstore dataset.
1. Place the ‘Sub-Category’ field on the Rows shelf and the ‘Sales’ field on the Columns shelf in your Tableau workspace.
2. Create a calculated field to isolate the sales for the ‘Paper’ sub-category using the formula:
IF [Sub-Category] = ‘Paper’ THEN [Sales] END. Then, drag this field to the Columns shelf.
3. Create an EXCLUDE LOD expression to exclude the sub-category dimension when calculating sales for the ‘Paper’ sub-category. This method replicates the ‘Paper’ sales figures across all other sub-categories.
{EXCLUDE [Sub-Category]: SUM([If Paper = Sales])}
4. Calculate the difference using the formula:
SUM([Sales]) – SUM([Exclude LOD Sub – Paper]).
Adding this calculation to the view allows us to see the difference in sales between ‘Paper’ and other product sub-categories.

That’s it for our journey through Tableau’s LOD expressions. We’ve seen how Fixed, Include, and Exclude LODs can transform data analysis, offering insights from various perspectives. I hope these insights have been as valuable for you as they have been for me. Keep exploring and uncovering the stories hidden in your data. Remember, mastering these expressions can truly elevate your data storytelling. So keep experimenting, keep learning, and most importantly, enjoy every discovery you make along the way in your data analysis adventures

Helpful links:

🖇️ Tableau Orders of Operations
🖇️ Create Level of Detail Expressions in Tableau
🖇️  INCLUDE Level of Detail Expressions
🖇️ EXCLUDE Level of Detail Expressions

 

Rodrigo Diaz
Author: Rodrigo Diaz

I'm Rodrigo from Mexico, and I am passionate about learning and career growth. I hold a law degree, a master's in business management, and a diploma in civil construction design. My expertise is in starting businesses and product design. I've worked in agriculture, IT, and the public sector, gaining insights into business operations. Outside work, I'm an avid golfer and enjoy spending time with my family, especially sharing experiences with my daughter.