In this video, I talk about a simple rule that can be of immense help in understanding how advanced calculations work in Tableau – if you properly understand the Golden Rule, you are well on your own way to figuring out how any advanced table calculation or LOD calculation actually produces the result.
Here is the gist of the video:
Before you understand the Golden Rule, you need to understand one more fundamental idea that is crucial in the reporting world: Level of Detail or granularity.
We can understand Level of Detail of a report by comparing it to the level of detail we see on a Google Map as we zoom in and out. As we zoom in, we see more detail and as we zoom out, we see less detail – the same thing applies to a report/chart. As we add more dimensions, we see more detail and as we remove dimension, we see less detail. Hence, Level of Detail is nothing but the combination of dimensions on the report.
But what does a Dimension mean for the database? What does the database do when you add one more Dimension to your report?
For the database, Dimension are equivalent to Filters. As you add a Dimension, the database adds a Filter. If you have 3 dimensions on your report, then the database has to add filters on each of the 3 dimensions to calculate the value of a cell in your report.
Hence, here is the Golden Rule: For each cell/mark,
- Find the Filters i.e. Level of Detail
- Apply the Filters
- Then do the Calculation e.g. SUM
In one-line, it can be written as “Filters THEN Calculation“
From this video onwards, I am not going to be producing a text version immediately. It is difficult for me to find large chunks of time to produce the videos and to write the text version. But as I find time, I will come back and update the post to have the text version – for now it is only going to be videos. I hope you find them to be of value.