A common analysis task in Tableau is to identify and describe exclude level of detail (LOD) expressions, which capture dimensions to omit (exclude) from the view level of detail. They are useful for removing unwanted information from the data by creating an EXCLUDE function to omit the dimension at which aggregation is not required. In this guide, you will learn how to describe and identify exclude (LOD) calculations in Tableau.
To construct an exclude (LOD) expression, dimensions are required against which you will aggregate the view information, along with the measure that will 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 look at two scenarios of how to implement exclude level of detail expression.
In the first scenario, say you want to visualize the annual sales for each segment and category with respect to the total sales.
To begin, drag
Category into the Columns shelf and
Order Date into the Rows shelf. To adjust the view, select the Fit Width option from the drop down in the tool bar as shown below.
Next, create a calculated field by going to the Analysis tab and selecting the Create Calculated Field option.
The next step is to write a calculation as shown below and name it
Exclude: Order Date. In this scenario, you want to exclude the
Order Date in the view.
Next, drag the measure names into Filter, select the options
Exclude: Order Date, and click OK.
After applying the filter, drag the measure names into the Rows shelf, next to the
Order Date variable, and measure values in the label of the marks card. This is shown below.
In the above output, you can see that the values of the excluded expression are replicated. This is because the total value remains unchanged as the
Order Date is not refined. Also, this helps in comparing the contribution of the individual year sales to the total sales across years.
In the second scenario, you will visualize the region level sales while excluding the state in the view. To start, create a calculated field by going to the Analysis tab and selecting the Create Calculated Field option.
The next step is to write the calculation as shown below and name it
Exclude: State. This will exclude the state and include the region while aggregating the sales.
The next step is to select the Map option in the Marks pane, and then drag the
State variables into Detail options of the Marks card. This is shown below.
Next, place the variable
Region into the Color option of the Marks pane.
Sales and the calculated expression into the Label option of the Marks pane. This will generate the following output.
The output above provides the comparison of the total sales per region and the contribution of each state in that region with respect to sales. You can format the visualization for readability and according to your preference.
In this guide, you learned how to create an exclude level of detail expression in Tableau. You also learned to apply it in two different scenarios. This skill will strengthen your descriptive analytics and visualization capabilities.
To learn more about visualization and data analysis using Tableau, please refer to the following guides: