Today, we're going to talk about using how Level of Detail Calculations work inside Tableau. This is an extremely complex topic that could easily span its own series. Alas, we'll try to touch on the basics. Let's start with a simple LoD. For this example, we'll use the Total Sub-Category Sales.
 |
| Total Sub-Category Sales |
Judging by the syntax, this LoD calculates the SUM( [Sales] ) at the Sub-Category level. What does it do with this information? First, it creates an underlying table for this calculation.
 |
| Total Sub-Category Sales (Underlying Table) |
We don't get to see this table, but it's still there. Since this is a FIXED calculation, it also takes into account any data source or context filters that may be applied. In this case, we don't have any of those. So, what happens next? This depends on the granularity of the chart that you're asking Tableau for. Let's start by decreasing the granularity (less rows in the chart) by choosing to aggregate by Category. Depending on your industry, this may also be referred to as going "Up the Hierarchy" or "Rolling Up".
 |
| Sales and Total Sub-Category Sales by Category |
As you can see, when you attempt to roll up this aggregation, it takes the underlying Sub-Category Sales table, and sums it up to the category level. Here's a rudimentary illustration.
 |
| Aggregation |
Generally, summing a sum is not the most useful operation. You can achieve the same result without the headache by just summing the values all the way through. However, this does become more useful when you want to see things like the max of the sums, or the sum of the maxes.
Now, let's move on to the next level, Disaggregating. Disaggregating is the exact opposite of Aggregation because you are increasing the granularity of the chart. This may also be known as "Rolling Down", "Drilling Down", or "Down the Hierarchy". To illustrate this, we'll use Manufacturer as our dimension.
 |
| Sales and Total Category Sales by Sub-Category and Manufacturer |
As you can see, the Total Sub-Category Sales are the same for every manufacturer within the Sub-Category. Why is this? Well, this chart has the Sub-Category dimension in it already. Therefore, Tableau simply takes our underlying table of Sales by Sub-Category, and appends it on to each row of the chart.
 |
| Appending |
These two processes are pretty simple. But, what if the dimension in your chart is completely unrelated to Sub-Category? Let's find out by using Segment.
 |
| Sales and Total Sub-Category Sales by Segment |
As you can see, the Total Sub-Category Sales is equal to the total sales (minus some rounding error). Since Segment and Sub-Category are "unrelated", the sum of the Total Sub-Category Sales is the total sales. However, what does it mean to be unrelated?
 |
| Total Sub-Category Sales by Segment and Sub-Category |
Turns out that every combination of Segment and Sub-Category exists in our context. Therefore, the sums will always be the same. Some of you might be saying "But my data has holes in it!" Holes are an extremely common part of data analysis and should always be considered. So, what happens if we swap out Segment for State?
 |
| Total Sub-Category Sales by State and Sub-Category |
As you can see, this cross-section has holes in it. So, what do you think happens when you remove Sub-Category from the canvas?
 |
| Total Sub-Category Sales by State |
You can see that most of the states don't add up to the Total Sales. This is caused by the holes. Here's a small illustration for you.
 |
| Incomplete Aggregation |
That's pretty much all the basics for LoDs. We've seen that when you use an LoD, Tableau creates an underlying table(s) and either appends or aggregates them to you chart. Don't worry though. There's plenty more amazing ways that Tableau uses LoDs. Hopefully, we've laid the groundwork for many LoDs to come. Thanks for reading. We hope you found this informative.
The workbook for this post can be found
here.
Brad Llewellyn
Business Intelligence Consultant
llewellyn.wb@gmail.com
http://www.linkedin.com/in/bradllewellyn
No comments:
Post a Comment