Practicing Level of Detail Calculations

When we talk about Level of Detail (LOD) calculations in Tableau, we mean three expressions: FIXED, INCLUDE, and EXCLUDE. These three expressions open up a world of options by providing the ability to create calculations that target specific levels of granularity. In older versions of Tableau, data granularity for a worksheet was established by the dimensions in a view. If the view contained dimensions for, for example, Region, State, and Postal Code, but the author wanted to create a City-level calculation, the City dimension would need to be included on the view. Furthermore, there was no mechanism for excluding or ignoring a given dimension on a view. Admittedly, the desired results could normally be obtained through some complex and sometimes convoluted use of table calculations, data blends, and so on. Fortunately, LODs greatly simplify these use case scenarios and, in some cases, enable what was previously impossible.

In this chapter...

Introducing LODs

Tableau’s default is to show measures in a view based on the dimensions also present in the view. If you have a dashboard with Sales data and dimensions like State and City, and you drag the State and Sales data onto the view, the Sales data will be divided by State, showing you Sales per State. If you want to divide the Sales data further into smaller chunks, you might add the City field, resulting in Sales data per City, per State. LODs can manipulate this default behavior.

After completing this chapter, you will be able to divide or partition measures by dimensions that are not in the view and show measures using fewer dimensions than are visible in the view.

To do this, we will build and use two playgrounds. Delivering reports as required by one’s job duties may lead to a thorough knowledge of a limited set of capabilities; that is, a deep but narrow understanding. It can be difficult to set aside time (and justify that time) to explore...

FIXED and EXCLUDE

The first playground we will build will be for the purpose of exploring two of the three LOD functions: FIXED and EXCLUDE. We will use a set of parameters and associated calculated fields to efficiently explore how these functions work.

Setting up the workbook

Much of the groundwork for this exercise has already been completed in the workbook associated with this chapter. The following steps will simply require you to open different calculations and parameters to see how they have been set up and why this works. Explanations are given along the way. If you do not have ready access to the workbook, you should be able to construct a similar one by referencing the following steps.

To complete the initial setup of a worksheet, take the following steps:

1. Navigate to https://public.tableau.com/profile/marleen.meier to locate and download the workbook associated with this chapter.
2. Open the workbook associated with this chapter and navigate to...

INCLUDE

The second playground is mostly set up for you when using the accompanying workbook (https://github.com/PacktPublishing/Mastering-Tableau-2023-Fourth-Edition/tree/main/Chapter07). We will, however, add some calculated fields for the purpose of effective exploration. If you do not have ready access to the workbook, you should be able to construct a similar one by referencing the following information.

Setting up the workbook

In the following, we will set up a worksheet with which we can practice the INCLUDE LODs:

1. Open the workbook associated with this chapter and navigate to the Exploring Include worksheet.
2. The parameters and calculated fields named 1st Dim, 2nd Dim, 3rd Dim, and 4th Dim created in the previous exercises are also utilized for this worksheet.
3. Right-click on the 1st Dim parameter and choose Duplicate.
4. Rename the duplicate Choose Included Dims.
5. Create a new calculated field named Case Include with the following code:...

Building practical applications with LODs

The first portion of this chapter was designed to demonstrate how LODs work. The remainder will be dedicated to practical applications. Specifically, we will consider three typical challenges that previously were solved using other Tableau capabilities, such as table calculations and data blending.

This exercise will look at a problem that occurs when mixing a table calculation that calculates the percentage of the total with a dimension filter. We will consider the problem, a solution using a LOD calculation, and finish with a commentary section on the germane points of the solution.

Using the LOD FIXED calculation

Did you ever encounter a situation where you wanted to calculate the Percent of Total while only showing a subset of the Total dimension in your view? Well, you are not alone. But thanks to LODs, you will soon know how to solve this problem. The following steps will guide you through the exercise:

1. Open the...

Summary

We began this chapter by exploring why LODs are so impactful and why their inclusion in Tableau was so lauded. Next, we built two playgrounds to explore how the three LODs—FIXED, EXCLUDE, and INCLUDE—work. Tableau’s default is to base calculations on the dimensions visible in the view. For example, if you have states in your view, the sales amount will be presented by state. If you are adding cities, the sales amount will be adjusted by state, by city. But, if you want to manipulate this default logic, you can use LODs. They allow you to calculate measures based on any dimension, no matter whether that dimension is represented in the view or not. We also saw that FIXED LODs are higher in the order of operations in Tableau than EXCLUDE and INCLUDE LODs. This is important to remember so that you use the correct LOD and/or filter in your dashboard.

In the next chapter, we’ll turn our attention to the visual side of Tableau and explore different...