Level of Detail Expressions, s1057 - Transcript

IfThenWhy™

IF: A standard visualization aggregates data based on the dimensions in the view, but you need to perform a calculation at a different, specific grain (such as a Zip Code or State level).

THEN: Utilize a FIXED LOD Expression—using the syntax {[FIXED] [Dimension] : [Aggregate]([Measure])}—to lock the calculation to the desired level of detail regardless of the dimensions present in the chart.

WHY: To provide the Peace of Certainty that your percentages and averages are accurate relative to the entire dataset, effectively reducing cognitive load by showing how individual marks relate to a larger total.

Hello! Today I want to look at Level of Detail (LOD) expressions. Here you can see my data source: I have a few rows of data across four columns, and I'm trying to show the "fans per restaurant."

The Problem: No LODs

In my first view, "No LODs," you see a basic chart with SUM(Fans) and AVG(Fans) on the Label tile. If you look at the marks—specifically Golden Corral—you see the fan count is 12, but the average is also 12. This is because Tableau is using the specific level of detail of this view to perform the average calculation.

That’s not what I want; I want to see the average for the entire Zip Code.

View 1: LOD by Zip Code

To start, I’ll duplicate the existing view and rename it LOD zip. I’ll remove the average fans from the Text tile, go to the Columns shelf, and change the measure from Sum to Average. Now I need to create two new fields:

  1. Fans Per Zip: In the Data pane, I’ll create a Calculated Field. This is an $LOD$ calculation, so it begins with a curly brace: {[FIXED] [Zip] : [SUM]([Fans])}. I’ll drag this new field onto Label.

  2. Percent Fans Per Zip: I’ll create another field defined as [Fans] / [Fans Per Zip]. I’m doing this to show that you can use an $LOD$ field within another calculation.

I’ll drag this second field onto Label and format it. By clicking the Label tile and then the ellipsis (...), I can clean up the text. To make the numbers look right, I’ll click the drop-down on the field in the Marks card, choose Format, and in the Format pane, set the numbers to Percentage.

Now, for Zip 32256, it correctly shows a total of 35 fans. Zaxby’s has 23 of those fans, which the calculation now correctly identifies as 65.71%.

View 2: LOD for All Fans

Next, I want to calculate these values at the "All" level (Statewide). I’ll duplicate the chart again and name it LOD All Fans. After removing the previous Zip calculations, I’ll create two new fields:

  1. All Fans: This $LOD$ uses the State dimension: {[FIXED] [State] : [SUM]([Fans])}. I’ll drag this to Label.

  2. Percent of All Fans: I’ll create this using a different method—building the $LOD$ inside the calculation: [Fans] / ({[FIXED] [State] : [SUM]([Fans])}).

This shows that the total fans across all zip codes is 40. Zaxby’s 23 fans now represent 57.5% of the total statewide fans.

An Alternate Method: The Abbreviated FIXED LOD

Finally, I want to show an alternate version of that last calculation, which I'll call Percent of All Fans V2.

In the formula [SUM]([Fans]) / [SUM]({[FIXED] : [SUM]([Fans])}), notice that there is no dimension listed after the FIXED keyword. When you leave the dimension blank, Tableau assumes you want to aggregate across all records in the data source. Since we only have one state in this data, this abbreviated version provides the same result as the state-level $LOD$.

And that is a basic overview of how to use FIXED LOD expressions in Tableau!

IfThenWhy™ is a proprietary methodology of Cathy Young.