Leveraging Level of Detail Calculations

Having considered row-level and aggregate calculations, it’s time to turn our attention to the third of the four main types of calculations: level of detail calculations.

Level of detail calculations (sometimes referred to as LOD calcs or LOD expressions) allow you to perform aggregations at a specified level of detail, which may be different from the level of detail that is defined in the view. You can leverage this capability to perform a wide variety of analyses that would otherwise be quite difficult.

In this chapter, we’ll cover the following:

• Overview of level of detail
• Level of detail calculation syntax and variations
• Examples of FIXED level of detail calculations
• Examples of INCLUDE level of detail calculations
• Examples of EXCLUDE level of detail calculations

Overview of level of detail

What does the term level of detail mean? A lot depends on the context in which the term is used. Within Tableau, we’ll distinguish several levels of detail, each of which is vitally important to understand in order to properly analyze data:

• Data level of detail: Sometimes referred to as the grain of the data, this is the level of detail defined by a single record of the data set. When you can articulate what one record of the data represents (for example, “Every record represents a single order” or “There is one record for every customer”), then you have a good understanding of the data level of detail. Row-level calculations operate at this level.
• View level of detail: We’ve previously discussed that the combination of fields used as dimensions in the view defines the view level of detail. Normally in a view, Tableau draws a single mark for each distinct combination of values present in the data...

Level of detail calculations

Before getting into practical examples of using level of detail calculations, let’s take a moment to understand the syntax and types of level of detail calculations.

Level of detail syntax

Level of detail calculations follow this basic pattern of syntax:

{FIXED|INCLUDE|EXCLUDE [Dim 1],[Dim 2] : AGG([Field])}

The definitions of the preceding declaration are as follows:

• FIXED, INCLUDE, and EXCLUDE are keywords that indicate the type of level of detail calculation. We’ll consider the differences in detail in the following section.
• Dim 1, Dim 2 (and as many dimensions that are needed) is a comma-separated list of dimension fields that defines the level of detail at which the calculation will be performed.
• AGG is the aggregate function you wish to perform (such as SUM, AVG, MIN, and MAX). If the field is already an aggregate calculation, you will not specify an additional aggregate function.
• Field...

Examples of fixed level of detail calculations

As we turn our attention to some practical examples of level of detail calculations, we’ll use the Chapter 05 Loans data set contained in the Chapter 05 workbook. The true data set contains many more records, but here is an example of the kinds of data it contains:

...

Date	Portfolio	Loan Type	Balance	Open Date	Member Name	Credit Score	Age	State
3/1/2020	Auto	New Auto	15987	9/29/2018	Samuel

Examples of include level of detail expressions

Include level of detail calculations can be very useful when you need to perform certain calculations at levels of detail that are lower (more detailed) than the view level of detail. Let’s take a look at an example.

Average loans per member

Some members have a single loan. Some have two or three or possibly more. What if we wanted to see how many loans the average member has on a state-by-state basis? Let’s consider how we might go about that.

We’ll start with a sheet where the view level of detail is State:

Figure 5.10: The starting place for the example—a filled map by state

It would be relatively easy to visualize the average credit score or average balance per state. But what if we want to visualize the average number of loans per member for each state? While there are several possible approaches to solving this kind of problem, here we’ll consider using the following level...

Example of exclude level of detail calculations

Exclude level of detail calculations are useful when you want to perform certain calculations at higher (less detailed) levels than the view level of detail. The following example will demonstrate how we can leverage this functionality.

Average credit score per loan type

In this example, we’ll answer the following question: how does the average credit score for a given loan type compare to the overall average for the entire portfolio?

Take the following view, which shows the average credit score per loan type (where loan types are grouped into portfolios):

Figure 5.13: This crosstab shows the average credit score per loan type

What if we wanted to compare the average credit score of each loan type with the overall average credit score for the entire portfolio? We could accomplish this with an exclude level of detail calculation that looks like this:

{EXCLUDE [Loan Type] : AVG([Credit Score])}

...

Summary

Level of detail expressions greatly extend what you can accomplish with calculations. You now have a toolset for working with data at different levels of detail. With fixed level of detail calculations, you can identify the first or last event in a series or whether a condition is ever true across entire subsets of data. With include expressions, you can work at lower levels of detail and then summarize those results in a view. With exclude expressions, you can work at higher levels of detail, greatly expanding analysis possibilities.

In the next chapter, we’ll explore the final main type of calculations: table calculations. These are some of the most powerful calculations in terms of their ability to solve problems, and they open up incredible possibilities for in-depth analysis. In practice, they range from very easy to exceptionally complex.

Join our community on Discord

Join our community’s Discord space for discussions with the author and other...