Level of Detail (LOD)

For this blog post, I thought I would write about the difference between different levels of detail (LOD). I wanted to do it in as few words as possible as a lot of websites give lengthy explanations and I think the end result is confusion and not clarity.

There are three types of LOD:

Fixed – This tells tableau to only consider the dimension you specify and nothing else

Include – This tells tableau to include the dimension specified as well as other dimension included in the columns and rows

Exclude – This Tells tableau to exclude the dimension specified from the calculations, even if it is on the rows or columns.

Examples:

The format of a LOD is as follows: Aggregate({LOD Dimension : Aggregate(Measure)})

Fixed

Simply this means that even if sub-categories is on the rows or columns the avg(profit) will only be computed for the category.

We aren’t telling it to exclude the dimension subcategory, although that is an unintended consequence. We are just saying to ignore anything not fixed. So the total column for the categories when subcategories are included will be all Tableau will output when LOD is fixed. If we add another dimension (like STATE) the result would still be the same, only looking at the category level. So Exclude and Fixed are quite similar to each other. I struggled for a while to understand the difference. But one day it finally clicked, that only the include LOD can increase the granularity, whereas Fixed and Exclude tend to decrease it.

Exclude

Why might we want to use an Exclude LOD? Well for one, I found it to be useful when calculating weighted averages. For example in the formula below

We know that Tableau will calculate the average of all dimensions it has, but will ignore the dimension subcategory as if it were not there.

In the example, if we take subcategories off the columns, we see that the total average is different. This happens because Tableau is seeing (4*8.7), (9*20.3) and (4*78.8) and dividing it by 19 (4+9+4) which yields 31.3. If the subcategories are taken off, it only does (8.7+20.3+78.8)/2 which yields 35.9. So as you can see there is some value in the exclude LOD.

Include

Include LOD as I mentioned earlier can increase the level of granularity so that the view can show the average profit per specified dimension without any calculations other than the LOD

Tableau cannot see what you don’t tell it to see. If we use the include LOD when subcategories aren’t on the columns then, in this case, the include LOD will act in the same way that the exclude LOD does yielding us a value of 84.0 this is calculated from (-3.5+17.5+237.9)/3 the totals were calculated from averaging the totals within the categories dimension. 64.4 was calculated from summing all values and dividing by 17 (number of dimensions).

I hope that this post may have cleared up any issues, thank you for reading.