Describe and Identify Exclude Level of Details Calculations in Tableau

May 18, 2020 • 6 Minute Read

Introduction

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.

Data

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, People, Orders, and 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.

Scenario 1

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 Segment and 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 Sales and 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.

Scenario 2

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 Country and 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.

Drag 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.

Conclusion

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: