Level of Detail (LoD) Calculations

Level of Detail Calculations

When you want to use a different level of detail than the visualization, use “level of detail expressions.” For example, the next visualization has “Sales” by “State/Province” and “City.” To see city sales as a percentage of state sales, I use level of detail expressions. The orange circles represent sales by City. I have a level of detail expression, “% of State Sales,” to calculate total sales for the “State/Province,” represented by blue circles. 

The deepest aggregation in the visualization is “City,” so SUM(Sales) for Calgary is 10,972. This is the visualization level of detail.

The total “Sales” for Alberta is 11,460. 

Calgary has 95.74% of sales for Alberta.

Before looking at a FIXED LoD expression, let’s take a moment to look at Tableau’s Order of Operations as it relates to LoDs. Tableau’s Order of Operations outlines how Tableau processes filters, calculations and other operations. When you see unexpected results when filtering, go back to the Order of Operations to review what Tableau is doing behind the scenes.

Fixed Level of Detail Expressions compute independently of the view and any dimension filters. “Fixed” LOD calculations are above Dimension filters in Tableau’s Order of Operations and are applied before dimension filters.

What are Level of Detail Expressions?

LOD expressions allow you to control the level of aggregation and dimensions used in a calculation. If a view shows a count of “Fans” per “Restaurant” and you also want to see the percentage of Fans compared to all Fans, a LOD calculation is the solution. 

A LOD calculation can use any field regardless of whether or not the field is part of the visualization. For example, a LOD calculation can include the State field; and the State field does not have to be part of the visualization or viz LOD.  

Scoping Keywords

LODs use three scoping keywords:

INCLUDE

EXCLUDE

FIXED

INCLUDE

The “Include” keyword changes to a more granular level than the view because it adds the specified dimensions to the dimensions already in the view. An INCLUDE LOD expression always returns a measure.

EXCLUDE

The “Exclude” is a less granular level that the view because it removes the specified view dimensions. An EXCLUDE LOD expression always returns a measure.

FIXED

FIXED only uses fields in the LOD declaration and ignores any fields in the view other than Context filters. In line with the Order of Operations we looked at earlier, FIXED also uses Data Source filters and Data Extract filters. A FIXED LOD expression can return a dimension or a measure. 

LOD Declaration

A LOD expression is enclosed in curly braces and follows this format.

{ Scoping Keyword Dimension Declaration : Aggregate Expression }

A sample LOD expression is shown below. The dimension field is “State.” This LOD expression tells Tableau to aggregate at the State level, regardless of whatever fields are already in the visualization. Here, the Aggregate Expression is SUM([Sales]).

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

• This LOD expression uses a FIXED scope.  

• The dimension declaration is the “State” field. 

• The aggregate expression is SUM([Sales]).

In the “dimension declaration,” multiple dimensions are separated by commas. There is a comma between “State” and “Restaurant” in this LOD.

{ FIXED [State], [Restaurant] : SUM([Sales]) }

• This LOD expression uses a FIXED scope.  

• The dimension declaration uses both the “State” and “Restaurant” fields. 

• The aggregate expression is SUM([Sales]).

Fixed LoD Example

In this workbook, we’ll look at the Viz LoD and two LoD calculations. The data source is very simple, with only a few rows of data to make it easier to follow the calculations.  

First, we’ll look at the “No LODs” worksheet and the “Count” and “Average” aggregations. Then we’ll look at percentage calculations on the “LOD Zip” and “LOD All Fans” worksheets.

Comparing Visualizations

Before we dive into creating this workbook, let’s look at the difference between a visualization with no LoDs and a visualization Before we dive into creating this workbook, let’s look at the difference between a visualization with no LODs and a visualization with a LOD calculation based on all records.

Project Files

If you want to follow along with this example, download the Tableau Public file.

Worksheets

There are three workseets in this workbook.

• No LODs

• LOD Zip

• LOD All Fans

No LODs

The first chart is a basic bar chart. The fields are shown below.

LOD Zip

This “LOD Zip” chart adds two LOD calculations.

LOD All Fans

The final worksheet uses two new fields.

Data & Fields

This workbook is based on a simple Excel file with four fields. I will also create several calculated fields. The data for this viz is shown below.



Fans

“Fans” is a  number field representing the number of fans of a particular restaurant, and is a continuous field. On all worksheets “Fans” is on the Columns shelf, as well as the Text tile on the Marks Card. I also use “Fans” in several calculated fields.  

State

The “State” field is a discrete dimension with a “String”  data type. As expected, the field has a blue background or “pill,”  because it is a discrete field. 

Zip

The “Zip” field is a discrete dimension with a “String”  data type. 

As expected, the field has a blue background  or “pill,”  because it is a discrete field. 

Restaurant

The “Restaurant” field is also a “String”  field on the Rows shelf. I also added a “nested” sort


Calculated Fields

Fans per Zip

The “Fans per Zip” field is used on the “LOD Zip” worksheet. This calculated field is only an LOD expression, as indicated by the curly braces. The scope is FIXED and the dimension declaration is “Fans.”

{ FIXED [Fans] : SUM( [Fans] ) }


% Fans per Zip

This calculated field computes a percentage using the “Fans Per Zip” LoD field. To see the LoD calculation you’d have to edit the “Fans per Zip” field. The outside enclosing parentheses are optional.

( [Fans] / [Fans per Zip] ) 


All Fans

The “All Fans” field is used on the “LOD All Fans” worksheet. This calculated field is only a LOD expression. The scope is FIXED and the dimension declaration is “State.”

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


% of All Fans

The “All Fans” field is used on the “LOD All Fans” worksheet. This calculated field uses an LOD expression inside another calculation. 

( [Fans] / { FIXED [State] : SUM( [Fans] ) } )


% of All Fans v2

This calculated field includes a LoD expression. This is another way to write the “% of All Fans” calculation. The scope is FIXED and there is no dimension declaration.

Sum( [Fans] ) / SUM( { FIXED : Sum( [Fans] ) } )


Components or Elements

There are several elements to this view.

• Columns Shelf

• Rows Shelf

• Marks Card: Label

Marks Card: Label

The fields on the Label tile on the Marks card vary depending on the worksheet.

No LODs

The “Fans” field is on the Label tile twice. One instance uses a SUM aggregation, and the other uses an AVG aggregation. 

Sum(Fans)

Avg(Fans)  


LOD Zip

There are three fields on the Label tile for the “LOD Zip” worksheet, and all three use a SUM aggregation.

Sum(Fans)

Sum(Fans Per Zip)  

Sum(% Fans Per Zip


LOD All Fans

There are three fields on the Label tile for the “LOD All Fans” worksheet, and all three use a SUM aggregation.

Sum(Fans)

Sum(% of All Fans

Sum(All Fans)  


The Viz Without LoD Calculations

For this bar chart, “No LODs,” I want to see a count of “Fans” for various restaurants by “Zip.” The “Fans” and “Zip” fields define the Viz level of detail or Viz LOD. 

1. “Fans” is on the Columns shelf and “Zip” and “Restaurant” are on the Rows shelf. 

2. Drag “Fans” onto Label tile of the Marks card. The default aggregation in Tableau is SUM(Fans) which is what I want.


3. Add a second instance of “Fans” to the Label tile on the Marks card and change the aggregation to “Average.” On a MAC, click-drag the “Fans” field from the Data pane onto the Text tile of the Marks card.  


4. Click the drop-down arrow of the “Fans” field on the Text tile to open the Field Context Menu. Select “Measure(Sum)” and change the aggregation to “Average.”


5. Click the Label tile on the Marks card and check “Show mark labels.” In the “Label Appearance” in the “Text” section click the ellipsis tool on the right to edit the text. 

To add fields within the text, click in the white box. Position the cursor where you want to place the field. In the top right corner of the “Edit Label” dialog window, click the drop-down arrow for “Insert” and select the field. Note, only fields on your view are shown in the Insert menu.

Now that you see the mark labels on the view, you’ll notice the average calculation is the same as the count because Tableau is using the Viz LOD. I actually want to see calculations based on ALL fans, and for that, I need a LOD calculation.\

The Viz with a LoD Zip Calculation

This view shows a count of Fans per Zip and Restaurant, but I also want to see the percentage of all Fans in the Zip.

1. First, I duplicate the “No LODs” worksheet. In the bottom tabbed area right-click on the sheet and choose “Duplicate.”


2. Change the aggregation of “Fans” on the Rows shelf to “AVG(Fans).” Click the drop-down arrow to open the Field Context Menu, select “Measure(Sum)” and change to “Average.”



3. For this view, I’m not using the field “AVG(Fans)” on the Marks card, and I delete that field.


4. Create the two calculated fields “Fans per Zip” and “% Fans per Zip.” 

The “Fans per Zip” is a LOD expression and shows the sum of Fans per Zip. 

• This LOD expression uses a FIXED scope.  

• The dimension declaration is the “Zip” field. 

• The aggregate expression is SUM([Fans]).


5. Next, I want to create a calculated field for the percentage, which uses the LOD field “Fans per Zip” I created in step four.

6. Sort “Restaurant” with a “Nested Sort.” 

Field Name: Fans per Zip

Aggregation: Sum



7. Add both fields to the Label tile on the Marks card. Click the Label tile on the Marks card and check “Show mark labels.” In the “Label Appearance” in the “Text” section, click the ellipsis tool on the right to edit the text. as shown in the following diagram.

Looking at the previous view, the fan total in the 32256 zip is 35, and the fan total in the 32092 zip is 5. 

% All Fans Sheet

While I could simply create one calculated field for this percentage, I’m going to make several calculated fields so you can see how I arrived at the final expression. 

1. Create the “All Fans” field. Notice I’m using the field “State” this time for the dimension declaration in the “% of All Fans” shown below. Add the field to Label on the Marks card. I’m not going to use this field anywhere else, I only want it on the view to illustrate the calculations.

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

2. Create the “% of All Fans” field. Add the field to Label on the Marks card. 

( [Fans] / { FIXED [State] : SUM( [Fans] ) } )


• This LOD expression uses a FIXED scope.  

• The dimension declaration is the “State” field. 

• The aggregate expression is SUM( [Fans] ).



While “State” is not part of the view, I can still use it in a LOD expression. When we looked at the data earlier, you probably noticed that all my restaurants are in the same state, so in essence, this calculation returns all data.


3. Finally, I’m going to combine these two fields into one expression called “% Fans per Zip LoD.” Add this new field to Label on the Marks card.

4. Edit the text as shown in step five earlier, inserting the new fields. The text should match the example  below.

Fixed LoD Without a Dimension Declaration

Another way to write the same expression for all records is shown below. This expression does not have a dimension declaration.

• This LOD expression uses a FIXED scope.  

• There is no dimension declaration after the FIXED keyword. 

• The aggregate expression in the LoD is SUM( [Fans] ).