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,
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.
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
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.
Next, drag the
Sub-Category variables into the Rows shelf.
The next step is to drag the calculated field created above into the Columns shelf.
Next, turn the Text label on, and sort in descending order. This will be fixed for each
Category. This is highlighted below.
Until now, you have kept both the
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.
In the second scenario, you will use three variables,
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
To begin, click on the Analysis tab and select Create Calculated Field, as in the previous scenario.
The next step is to write the fixed LOD expression and name it
Fixed LOD: Sales Per Segment and Category.
The next step is to drag the variables
Category into the Rows shelf and the calculated expression into the Columns shelf.
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.
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
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
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
Category. This is reinforced in the two images below.
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: