Working with different levels of detail

Remember that the two keys of good are as follows:

  • Having a level of detail that is meaningful
  • Having measures that match the level of detail or that are possibly at higher levels of detail

Measures at lower levels tend to result in wide data, and can make some analysis difficult or even impossible. Measures at higher levels of detail can, at times, be useful. As long as we are aware of how to handle them correctly, we can avoid some pitfalls.

Consider, for example, the following data (included as Apartment Rent.xlsx in the Chapter 9 directory), which gives us a single record each month per apartment:

The two measures are really at different levels of detail:

  • Rent Collected matches the level of detail of the data where there is a record of how much rent was collected for each apartment for each month.
  • Square Feet, on the other hand, does not change month to month. Rather, it is at the higher level of apartment only.

This can be observed when we remove the date from the view and look at everything at the apartment level:

Notice that the Sum(Rent Collected) makes perfect sense. You can add up the rent collected per month and get a meaningful result per apartment. However, you cannot Sum up Square Feet and get a meaningful result per apartment. Other aggregations, such as average, minimum, and maximum, do give the right results per apartment.

However, imagine that you were asked to come up with the ratio of total rent collected to square feet per apartment. You know it will be an aggregate calculation because you have to sum the rent that's collected prior to dividing. But which of these is the correct calculation?

  • SUM([Rent Collected])/SUM([Square Feet])
  • SUM([Rent Collected])/AVG([Square Feet])
  • SUM([Rent Collected])/MIN([Square Feet])
  • SUM([Rent Collected])/MAX([Square Feet])

The first one is obviously wrong. We've already seen that square feet should not be added each month. Any of the final three would be correct if we ensure that Apartment continues to define the level of detail of the view.

However, once we look at the view that has a different level of detail (for example, the total for all apartments or monthly for multiple apartments), the calculations don't work. To understand why, consider what happens when we turn on column grand totals (from the menu, select Analysis | Totals | Show Column Grand Totals or drag and drop Totals from the Analytics tab):

The problem here is that the Grand Total line is at the level of detail of all apartments (for all months). What we really want as the Grand Total of square feet is 900 + 750 = 1,650. But here, the sum of square feet is the addition of square feet for all apartments for all months. The average won't work. The minimum finds the value 750 as the smallest measure for all apartments in the data. Likewise, the maximum picks 900 as the single largest value. Therefore, none of the proposed calculations would work at any level of detail that does not include the individual apartment.

You can adjust how sub totals and grand totals are computed by clicking the individual value and using the drop-down menu to select how the total is computed. Alternatively, right-click the active measure field and select Total Using. You can change how all measures are totaled at once from the menu by selecting Analysis | Totals | Total All Using. Using this two pass total technique could result in correct results in the preceding view, but would not universally solve the problem. For example, if you wanted to show price per square foot for each month, you'd have the same issue.

Fortunately, Tableau gives us the ability to work with different levels of detail in a view. Using Level of Detail (LOD) calculations, which we encountered previously in Chapter 4, Starting an Adventure with Calculations, we can calculate the square feet per apartment.

Here, we'll use a fixed LOD calculation to keep the level of detail fixed at apartment. We'll create a calculated field named Square Feet per Apartment with the following code:

{ INCLUDE [Apartment] : MIN([Square Feet]) }

The curly braces surround a LOD calculation and the key word INCLUDE indicates that we want to include Apartment as part of the level of detail for the calculation, even if it is not included in the view level of detail. MIN is used in the preceding code, but MAX or AVG could have been used as well because all give the same result per apartment.

As you can see, the calculation returns the correct result in the view at the apartment level and at the grand total level, where Tableau includes Apartment to find 900 (the minimum for A) and 750 (the minimum for B) and then sums them to get 1,650:

Now, we can use the LOD calculated field in another calculation to determine the desired results. We'll create a calculated field named Rent Collected per Square Foot with the following code:

SUM([Rent Collected])/SUM([Square Feet per Apartment])

When that field is added to the view and formatted to show decimals, the final outcome is correct:

Alternatively, instead of using INCLUDE, we could have used a FIXED level of detail, which is always performed at the level of detail of the dimension(s) following the FIXED keywords, regardless of what level of detail is defined in the view. This would have told Tableau to always calculate the minimum square feet per apartment, regardless of what dimensions define the view level of detail. While very useful, be aware that FIXED level of detail calculations are calculated for the entire context (either the entire dataset or the subset defined by context filters). Using them without understanding this can yield unexpected results.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.208.97