In previous chapters, we learnt about how our Measures are dependent on the Dimensions that are present in our view. These Dimensions act as independent variables, whereas the Measures are dependent on those Dimensions. For example, imagine having a horizontal bar chart that is showing Sales across Region. Since our Orders sheet of Sample - Superstore.xlsx data has four regions, we will get a bar chart showing four bars and the length of the bar representing the sales across regions. Now, if we get another Dimension, say Category, and place it right after the Region field in the Rows shelf, we will add an additional granularity of Category into the view and now instead of 4 bars, we will get 12 bars as we have three categories in our data and at this point, the Sales will be computed for each Category within a Region. Also, in the same view, if we remove Region, then we will have a bar chart with three bars showing sales across categories without any regional granularity.
What this means is that when we add a Dimension into the view or remove a Dimension from the view, we are essentially adding or removing certain granularity from our view. In the versions prior to version 9.0 of Tableau, we had to make sure that the necessary Dimensions are kept in the view to maintain that granularity. However, with the Level of Detail (LOD) calculations we can add the granularity that we want without having to call the necessary Dimensions in the view. This can also work if we wish to remove a certain granularity as well. In other words, LOD calculations help us set the level of detail that we want for a particular field regardless of what is in the view.
There are three different types of LOD calculations, and they are as follows: INCLUDE, EXCLUDE, and FIXED:
- INCLUDE: Computes values using the specified dimensions in addition to whatever dimensions are present in the view
- EXCLUDE: Computes the values by omitting the dimensions from the view's level of detail
- FIXED: Computes values using the specified dimensions without reference to any other dimensions in the view
We will take a look at all three functions in the following recipes. However, before getting into these functions, let us revisit our recipe of table calculations where we saw an example of calculating the percent of total. Now, depending on what percentages we wanted to see, we changed the direction of our calculation to either give us percentage with respect to the Row Total, or with respect to the Column Total, or with respect to the Grand Total. In that case, when we wanted to show the percentage with respect to the Grand Total, we simply changed the direction of our table calculation to Table instead of Table (across) or Table (down). This worked out well, but imagine having another view with different set of dimensions where we need to do the same exercise of showing percentages with respect to the Grand Total. In this case too, we will first have to design our view, make sure that we don't change the same, and then do a table calculation on the measure and make sure that the scope and direction of this table calculation is well defined. If we decide to use table calculations, then we will have to do this exercise every time for all the different views where we wish to show the percentage with respect to the Grand Total, and this can be cumbersome. So, the question is that instead of having to compute the Grand Total on the fly every time, is it possible for us to maybe have the Grand Total pre-computed and saved for later use? This can be achieved by using the Level of Detail (LOD) calculations in Tableau. Let us see how this objective can be achieved using LOD calculation.
Let us use the same example as that of our Table calculation recipe, where we used the Sales, Region, and Category fields from the Orders sheet of Sample - Superstore.xlsx
data. Let us get started.
- We will begin by duplicating the sheet called Table Calculations-percent of total and renaming it LOD-percent of total.
- Let us then remove the Percent of Total Sales calculation from the Text shelf.
- We will then right click on the Sales field in the Measures pane and select the Create | Calculated Field… option. Let us call this calculation LOD-Percent of Total Sales and type SUM([Sales])/SUM({SUM([Sales])}).
- Refer to the following screenshot:
- Let us click OK. Now, if we double click on this calculation and get it in the view, we will see some decimal numbers, as shown in the following screenshot:
- Now, let us convert these decimals numbers to percentages, and to do so, let's right click on LOD-Percent of Total Sales in the Measures pane and select the Default Properties | Number format… option. We will select the Percentage option with two decimals. Refer to the following screenshot:
- Once we do that, our view will update to show the exact same percentages as that of what we saw in the Table calculation recipe. Refer to the following screenshot:
Table calculations are performed on the fly in Tableau after the data is already fetched in the view, thus making them dependent on the table structure. This means that if we add or remove any dimension from our view, the table calculation will not give us the same values as it was before adding or removing that new dimension. However, in the case of LOD calculations, the computation happens independent of our view.
In the preceding recipe, we are essentially dividing our sales with the total sales; however, instead of using the TOTAL() function, which is a table calculation, we are using the SUM({SUM([Sales])}). What this does is that it computes the total sales regardless of what Dimensions we have in the view.
The SUM([Sales]) returns sales for each category in each region, whereas the SUM({SUM([Sales])}) returns the total sales of the entire data source.
All LOD calculations are written within the curly brackets and one needs to start the LOD calculation using the opening curly bracket and terminate the calculation using the closing curly bracket.