To follow this recipe, open B05527_03 – STARTER.twbx. Use the worksheet called Compare one against everything else and connect to the Top Athlete Salaries (Global Sport Finances) data source.

The following are the steps to create a chart that allows a selected item to be compared with all other items in the view:

It is not uncommon to want to compare one item against all other items in a chart or viz. This has become easier to achieve in Tableau, thanks to the addition of LOD expressions starting in V9.

In this recipe, we wanted to compare how much more or less athletes are making compared to one athlete we select. This may seem to be a simple calculation, but it quickly becomes tricky if we recall how Tableau works. The grain (or granularity) of measures in Tableau by default depends on the dimensions that are present in the view. For example, the granularity of the following measure SUM(Salary/Winnings $) in the Columns shelf is per Sport.

If another dimension is added, this means SUM(Salary/Winnings $) is further sliced. In the following example, it means that the measure is now by Sport and by Athlete.

By default, the level of detail presented in a Tableau chart is affected by the data fields used in any of the cards and shelves. However, with LOD expressions, the level of detail can be different from what is in the canvas.

Let us start by attempting to display the salary differences between the selected athlete in the Compare salary with parameter and the rest of the athletes using a simple SUM formula:

When we select Kobe Bryant, we can see that we are not able to display his salary side by side with other athletes using the non-LOD calculated field. His salary appears to be zero for everyone else but him.

This is problematic because this will give us incorrect salary differences.

However, using a FIXED LOD expressed, we can keep this number static. We can use the previous formula and just enclose it in curly braces.

The syntax of LOD expressions is:

{[FIXED | INCLUDE | EXCLUDE] <dimension declaration > : <aggregate expression>}

The FIXED keyword can be omitted if the LOD expression is not being fixed to any specific dimensions. When we display this in our view, we can see that Kobe Bryant's salary is persisted for each row.

Calculating the difference is now easier, because the calculated field with LOD expression maintains the chosen athlete's salary.

You are probably wondering why we are enclosing the Selected Athlete Salary calculated field in SUM when it is already a SUM. The reason is because as far as Tableau is concerned, an LOD expression is still a non-aggregated field. If we tried to use this without an aggregation and subtract this from an aggregated expression, we would get the classic error on mixing aggregate and non-aggregate fields:

The good thing is we simply need to enclose the field with the LOD expression in an aggregated function, like a SUM, MIN, or MAX. The SUM, MIN, or MAX of one value is still that value, so it will not affect the calculation and it will allow us get around the mixed aggregation error.

One more thing we did in this recipe is create another calculated field for a text message we are displaying as a column. This calculated field, called Message, uses several IF…ELSEIF statements to see if the salary difference is more, less, or equal, and composes the appropriate string message to be displayed:

LOD expressions were introduced in Tableau V9—one of most anticipated, if not the most anticipated, features of this release.

LOD expressions are very powerful, and allow us to simplify what used to be complicated calculations from before their introduction. The problem we solved in this recipe might have required a blend, or a subquery at the data source, had we not had access to LODs.

The syntax for LOD expressions is as follows:

{[FIXED | INCLUDE | EXCLUDE] <dimension declaration > : <aggregate expression>}

While powerful, one of the limitations is that the dimension declaration must be a persistent field. We can definitely get around this, but this requires creating additional fields that may be used only within the LOD expression.

Tableau V10 now allows expressions to be used instead of dimension field names. For example, if you wanted to fix sales per year, you can simply use the YEAR function in your LOD expression:

{ FIXED YEAR([Order Date]):SUM([Sales])}

There are many scenarios where LOD expressions can prove to be useful and efficient. If you're new to LOD expressions, or looking to understand them better, I encourage you to check out the following resources: