The level of detail calculations (LOD) enable you to tell Tableau Public at exactly which dimensional level you would like to aggregate without having to place that dimension on the visualization. Additionally, you can include or exclude dimensions in the calculations and create calculations that include all the underlying data.
The following are the three types of LOD calculations:
LOD has a big caveat. While you can perform table calculations on them and create aggregations and functions within them, you cannot include table calculations in them.
Each LOD calculation has the following three features that are different from those that we have discussed before:
- The LOD expression type: In this case, the LOD expression type is FIXED
- The Dimension on which the calculation is operating (you can add dimension levels, which should be arranged in the increasing order of granularity by separating them with a comma): In this case, it is Region
- The Aggregation: In this case, it is COUNTD([Country Name])
Note that the entire expression is enclosed in curly braces, as shown in the following screenshot. The LOD calculations are the only instances in Tableau Public where you will use curly braces:
This field provides us with the unique number of countries in each region, even though 80 percent of these countries are filtered out. This provides great context.
In the following example, we will discuss:
- Add this field, which is a measure, to the Rows shelf to the right of Country.
- Then, click on its Context menu and select Discrete instead of continuous so that it becomes a dimension.
- Drag the field so that it's to the right of Region and to the left of Country, as shown in the following screenshot:
This visualization shows us the bottom 20 percent of the 53 countries in Africa in the data set. It would be even more helpful to know what the overall access to improved water sources is in each region and not just by country.
The following are a few issues that we need to overcome with virtualization:
- The data is not necessarily set up for this.
- The granularity of the data is at the country level, and we only know the percentage in each country that has access to clean water. We don't know the discrete number.
- We do not have the totals by region. We need to determine the overall number of people with access to clean drinking water, and then we need to divide this number by the population of each region.
In the following example, we will show you how to solve some problem by using an INCLUDE LOD calculation nested within a FIXED LOD calculation, and each will be aggregated. This is an issue that most people have had to solve by using data sources with different levels of aggregation, which takes a lot of time to develop and render in Tableau Public.
Create a new field within the World Development Indicators data source and name it Population with Access to Improved Water. It's kind of a long name, but we want to ensure that no one misunderstands the metric.
So, here's our approach to calculating the overall percentage of each region with access to clean water:
- The primary LOD type is FIXED at the Region level.
- The numerator (enclosed in the red box shown in the following screenshot) adds up the total population that has access to clean water.
- The secondary LOD type is INCLUDE at the Country Name level. We are using INCLUDE because we want to roll up the country-level data to the region.
Rationale: For every country, we need to know the total population that has access to the sources of clean water. We have the percentage of the population. So, we need to multiply the maximum percentage with the total population to get the number of people who have access to clean water.
- The denominator (enclosed in the blue box in the following screenshot) is the total population. We need to know the total population by region. So, we add up the total population of each country.
- We will use the
INCLUDEfunction at the Country Name level and then use theMAXaggregations. The granularity of the data is at the Year level. We are not including Year, because that would produce the population at the Country Name and Year level. We just want the total population of each country, as shown in the following screenshot:
This is a long field, but it accomplishes what has been difficult before, namely aggregating at multiple dimensional levels in one calculation without duplications, and aggregating records that are not a part of the visualization.
Add this field to the Rows shelf of the previous bar chart, turn it into a discrete field, and drag it to the left of Country Name, just as we did for Country Count.
Also, right-click on the new calculated field, click on Default Properties, and then select Format so that we can set the number format as a percentage.
Here's the finished product. Only 69 percent of the people in Africa have access to clean drinking water:
