Tableau Deep Dive: LOD – The Include Calculation

Data

Tableau Deep Dive: LOD – The Include Calculation

Tableau Deep Dives are a loose collection of mini-series designed to give you an in-depth look into various features of Tableau Software.

In the previous article of this Deep Dive on LODs, we talked about Detail in general for Tableau Desktop. In Part 2, we’ll explore the Include level of detail calculation.

Start With the Data

Before I go into Tableau Desktop, I always think it’s foundationally important to discuss what we are trying to do in terms of the data itself. Tableau is great for doing the heavy lifting when it comes to aggregation and calculations, but it’s always up to us to perfect the logic first.

Let’s assume a very basic table with the following fields: OrderID, Customer Name and Sales. Here’s a snippet of our data, the first twelve rows:

Basic table

For our example, let’s make our aggregation an average. Our highest level of aggregation, and thereby the least amount of granularity, is to simply average all twelve records of our sample data set. This also corresponds to our OrderID field, which would be our primary key for this table; so OrderID is also the highest level of detail by default.

Averaging by sales

I summed the entire column and then divided it by the total number of rows, which is $3,059.00 divided by 12. The result of this aggregation would be an average sales per record of $254.92.

Inside of Tableau Desktop, this looks like this:

AVG(Sales) in Tableau

Let’s increase our level of detail by also considering the other dimension in our sample data source, Customer Name. My question that I want Tableau to answer is:

What is the average amount of sales per customer?

Let’s re-sort our table by Customer Name:

Resorted table by Customer Name

Now let’s group all of our orders by Customer Name and sum the sales by customer:

Group by Customer Name and sum of sales by customer

Now, we can average as we did before by summing the entire column and dividing by the new number of rows. In this case, my denominator is going to be eight customers rather than the 12 total records. The result is $382.38 per customer.

I have changed my level of detail. Prior to level of detail calculations, to derive the average sales per customer calculation would have meant including Customer Name into your visualization. I’ve reached the same number by including a Grand Total aggregated on average in this example using Customer Name as my dimension:

Sum(Sales) + Customer Name in Tableau

Let’s see how we can accomplish that same calculation inside of Tableau without adding Customer Name to the visualization.

Level of Detail Syntax

But first … let’s visit the syntax that Tableau wants for our LOD calculation. In the Calculated Field editor, Tableau Desktop color codes the different elements of your calculation. Functions are in blue. Fields are in orange. Parameters are in purple. LOD is in dark blue.

Here’s our syntax:

{INCLUDE [dim1]: aggregate expression}

Tableau will offer to autocomplete your LOD as you are typing. It will not however, add the end bracket. Make sure to add that or you’ll get the following error message:

Tableau says

You can use additional dimensions within your LOD calculation by adding a comma between them, like so:

{INCLUDE [dim1], [dim2] : aggregate expression}

Include LOD

Here’s how we would reach the same level of detail for our average sales per customer example above within Tableau Desktop. First, we create a calculated field, which I’ve named LOD Sales Include Customer Name. Remember, we need to first sum our measure (Sales) on our new level of detail (Customer Name):

{INCLUDE [Customer Name]: SUM([Sales])}

Now, we can aggregate our calculated field using average when we use it in the visualization. Here’s how it looks inside of Tableau:

Include Calculation in Tableau Success!

Success! The average sales across all orders is correct ($254.92) and the average sales per customer is also correct ($382.38).

Note that we haven’t used Customer Name in any of the other three areas where you would define your level of detail – Rows, Columns or Detail. We used an LOD calculation to Include customer name in the level of detail. You can see that we’ve got a much greater degree of flexibility and control in our visualizations.

The next topic we’ll cover in Part 3 is the Exclude option. Remember, leave comments or questions in the space below. Cheers!

Want More Tableau Deep Dives

  1. Tableau Deep Dive: LOD – Introduction to Detail
  2. Tableau Deep Dive: LOD – The Include Calculation
  3. Tableau Deep Dive: LOD – The Exclude Calculation
  4. Tableau Deep Dive: LOD – The Fixed Calculation
  5. Tableau Deep Dive: LOD – LOD Calculations vs. Table Calculations
  6. Tableau Deep Dive: Parameters – Parameter Overview
  7. Tableau Deep Dive: Parameters – Parameter Properties
  8. Tableau Deep Dive: Parameters – Filtering – Top N
  9. Tableau Deep Dive: Parameters – Calculated Fields
  10. Tableau Deep Dive: Parameters – Filtering Across Data Sources
  11. Tableau Deep Dive: Parameters – Bins
  12. Tableau Deep Dive: Parameters – Reference Lines
  13. Tableau Deep Dive: Parameters – Table Calculations
  14. Tableau Deep Dive: Sets – Introduction to Sets
  15. Tableau Deep Dive: Sets – Constant Sets
  16. Tableau Deep Dive: Sets – Computed Sets
  17. Tableau Deep Dive: Sets – IN/OUT
  18. Tableau Deep Dive: Sets – Combined Sets
  19. Tableau Deep Dive: Sets – Calculated Fields
  20. Tableau Deep Dive: Sets – Hierarchies
  21. Tableau Deep Dive: Dates – Introduction to Dates
  22. Tableau Deep Dive: Dates – Preparing Dates
  23. Tableau Deep Dive: Dates – More Date Functions
  24. Tableau Deep Dive: Dates – Exact Dates
  25. Tableau Deep Dive: Dates – Custom Dates
  26. Tableau Deep Dive: Dates – Rolling Dates
  27. Tableau Deep Dive: Dates – Calendar Filters
  28. Tableau Deep Dive: Dates – Week-by-Week Comparison
  29. Tableau Deep Dive: Dashboard Design – Planning
  30. Tableau Deep Dive: Dashboard Design – Layout & Structure
  31. Tableau Deep Dive: Dashboard Design – Proof of Concept
  32. Tableau Deep Dive: Dashboard Design – Adding Interactivity
  33. Tableau Deep Dive: Dashboard Design – Visual Best Practices
  34. Tableau Deep Dive: Dashboard Design – Optimization & Governance
  35. Tableau Deep Dive: Dashboard Design – Publishing
  36. Tableau Deep Dive: Table Calculations – Custom Sorts, Part One
  37. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Two
  38. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Three

More About the Author

Robert Curtis

Managing Director, APAC
Building Solutions with InterWorks at Corinium’s Data Architecture Conference in Melbourne InterWorks was a proud sponsor of the Data Architecture Conference hosted by Corinium in Melbourne on 21 and 22 June 2023. Hundreds of ...
Virtual Event Recap: Deep Dive of Tableau Parameters For our June Lunch and Learn, we did a deep dive on parameters for our friends across Australia and Singapore. Tableau is the premier ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!