Tableau Deep Dive: LOD – The Exclude Calculation

Data

Tableau Deep Dive: LOD – The Exclude 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.

This is the third article in our Tableau Deep Dive on level of detail. Part 1 reviewed the idea of detail. Part 2 introduced level of detail calculations and examined the Include LOD calculation. Let’s now jump into Exclude.

A Use Case

For our examples in this article, we’ll use the Superstore Sales Training sample data source that you can find in either the Tableau Fundamentals or Tableau Advanced classes. Let’s build the following view:

Basic Tableau view

We’re looking at the sum of sales by City further segmented by State and Country/Region with a USA-only filter. If I wanted to show my sales by state or country alongside sales by city, it would prove to be particularly difficult. Even re-ordering my dimensions has little effect aside from how the viz is organized.

Regardless, the Sales column has the exact same values as our previous view:

Re-ordered dimensions in our Tableau view

The only way we could accomplish this is by creating Totals for each pane, which would then sum the state at the bottom.

Exclude LOD Calculation

We can use the Exclude level of detail calculation to accomplish this requirement. Let’s start by excluding the City dimension. Remember our example with the Include LOD calculation? We must first sum our results within the calculation and then allow Tableau Desktop to use the average aggregation type in the view. Here’s our calculation, named LOD Sales Exclude City:

{EXCLUDE [City]: SUM([Sales])}

By using this calculation, we are essentially removing City from our view for the purpose of our LOD calculation to determine a value based on the State’s level of detail. In other words, this calculation is mimicking this type of view:

The Tableau view we're mimicking

We can use the above viz to validate our LOD calculation to make sure its returning the value that we are expecting. Let’s add our LOD calculation to our initial view with the Country/Region, State and City dimensions using the average aggregation type.

Adding our LOD calculation to the Tableau view

Eureka! We can see that our LOD calculation is returning the correct sum on the State level. Let’s take this to another level … literally. We’re going to use our Exclude LOD calculation to not only exclude City but also State to get the sum of the Country/Region dimension. As a guide, here’s the equivalent view for what our calculation is going to produce:

Equivalent Tableau view for our LOD calculation

And here’s our calculation to exclude both City and State from our original view:

{EXCLUDE [City], [State]: SUM([Sales])}

By excluding City and State, our LOD calculation gives us the following result:

Result of our Exclude LOD calculation in Tableau

To conclude, let’s add both of our LOD calculations to our views – one excluding just City and the other excluding both City and State. We can also edit the Column header alias by right-clicking on the name:

Edit Column header

We’ll rename them Country Sales, State Sales, and City Sales respectively. Here’s our final view with both of our LOD calculations:

Final view of both LOD calculations in Tableau

Missing Dimensions?

You might be wondering what will happen if I exclude a field in my LOD calculation that isn’t in the view? The simple answer is nothing will change in your view. This is because you are telling Tableau to remove a dimension that does not exist, so it will return the same result as if you never used the Exclude LOD calculation in the first place. Both Include and Exclude return results relative to your visualization, which means it matters what dimensions you have included in your view due to how each of these LOD calculations function.

To illustrate this point, let’s take our view from above and remove the Country/Region, State and City dimensions from the Rows shelf and replace it with the Department dimension. We’ll leave the Sales measure along with our two LOD calculations that exclude City as well as City and State.

Replacing with Department dimension

Tableau is performing the calculation exactly as intended; but since none of the excluded dimensions are in the view, it returns the exact same value as sum of Sales outside of the LOD calculation.

In the next article of the LOD Tableau Deep Dive, we’ll examine an LOD calculation that is not relative to the view you’ve created. This is the Fixed calculation. As always, we appreciate any thoughts or feedback in the Comments section below!

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

Analytics Consultant | ANZ Practice Lead
Tableau 2019.3 Spotlight: Tableau Catalog In my earlier article highlighting the new features of Tableau 2019.3, I touched very briefly on Tableau Catalog. I’ll spend a bit more ...
Tableau 2019.3 Highlight: Our Favorite Features The latest version of Tableau has just been released, and honestly, 2019.3 feels like a major release rather than an in-year update. It ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

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