A common analysis task in Tableau is to describe and identify a fixed level of detail (LOD) expression. These expressions compute a value using the specified dimensions, without reference to the dimensions in the view. They are useful for segmenting the analytics workflow and allow us to define different levels of granularity within the same chart for a specific measure.
In this guide, you will learn how to describe and identify a fixed LOD calculation in Tableau.
To construct a fixed LOD expression, a dimension is required, against which we’ll aggregate the view information and the measure that’ll be aggregated.
This guide will use the sample Superstore data source available in the Tableau repository. There are three sheets, People
, Orders
, and Return
, that have been joined to fetch the data.
In this guide, you’ll walk through two scenarios of how to implement fixed level of detail expressions.
In the first scenario, you want to check the profit generated by each product category. In the view, you want to see both product and sub product categories. However, the profit must be displayed with respect to each product category, and not subcategories. To begin, click on the Analysis tab and select Create Calculated Field.
Output:
The next step is to write the fixed LOD expression to show the profit for every category. To do this, you’ll use the variables Profit
and Category
. This is shown below where the calculated field is named as Fixed LOD - Profit Per Category
. Note that the curly braces are mandatory, otherwise it will give an error.
Output:
Next, drag the Category
and Sub-Category
variables into the Rows shelf.
Output:
The next step is to drag the calculated field created above into the Columns shelf.
Output:
Next, turn the Text label on, and sort in descending order. This will be fixed for each Category
. This is highlighted below.
Output:
Until now, you have kept both the Category
and Sub-Category
variables in the Rows shelf. Even if you remove Category
from the view, the measure will be calculated based on the dimension specified in the expression. This means that the changes in the view do not impact the aggregation of the measure Profit
. To understand this better, place the variable Category
in Color. This will not change the measure aggregation or the values, as shown below.
Output:
In the second scenario, you will use three variables, Sales
, Segment
, and Category
. In this scenario, the fixed level of detail expression will contain more than one dimension against the measure. This means that the output will display the aggregation for Sales
with respect to Segment
and Category
.
To begin, click on the Analysis tab and select Create Calculated Field, as in the previous scenario.
Output:
The next step is to write the fixed LOD expression and name it Fixed LOD: Sales Per Segment and Category
.
Output:
The next step is to drag the variables Segment
and Category
into the Rows shelf and the calculated expression into the Columns shelf.
Output:
The output above considers both dimensions specified in the LOD expression. However, if we remove the variable Category
from the view and just keep Segment
in the Rows shelf, you’ll see that the value has changed. This is because earlier it was breaking up the aggregation at the Category
level as specified in the expression.
Output:
The values displayed above are not wrong, but they are only aggregated at the segment level. To understand this more clearly, make a new sheet and drag the variables Segment
and Category
back into the Rows shelf. Then, click on the Analysis tab, go to Totals, and select the option of adding all subtotals. This will create the output below that gives the totals for each Segment
and Category
combination.
Output:
Now, if you match the total values of the previous two images, you will see that the total is same. This means that even after removing the Category
from the view, the fixed LOD expression is still fixed to both Segment
and Category
. This is reinforced in the two images below.
Output:
Output:
In this guide, you learned how to create a fixed level of detail expression in Tableau. This will strengthen your descriptive as well as diagnostic analytics capabilities. To learn more about visualization and data analysis using Tableau, please refer to the following guides: