Tableau Deep Dive: LOD – The Fixed Calculation

Data

Tableau Deep Dive: LOD – The Fixed 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 Part 4 of our Tableau Deep Dive on level of detail, we’re going to examine the last of the three LOD calculation types – Fixed. Remember: This series is building upon each article, and I highly recommend that you start with Part 1 and go forward in order.

Fixed LOD Calculation

Both the Include and Exclude calculation types are relative to the visualization in which they are used. If you Include a dimension that is already in the view, then the Include calculation will have no extra effect. Likewise, if you Exclude a dimension that is not in the view, then that calculation will have no effect.

Fixed LOD calculations are not relative at all. Instead, we tell the calculation to only focus on the dimension(s) that we specify in the calculation regardless of what is and what is not in the visualization. To illustrate this example, we’ll build the same view that we had in Part 3 (Exclude):

Basic Tableau view

For our imaginary use case, we want to show the sum of Sales for State and for Country. We achieved this in our Exclude example, by excluding City and then City and State. The strength of the Fixed LOD calculation is that we don’t have to anticipate what dimensions are or are not in the view to accomplish the same result. We simply fix the level of detail on the dimension(s) that we want.

Here’s how the calculation for the sum of Sales for State would look, named LOD Sales Fixed State:

{FIXED [State]:SUM([Sales])}

This calculation is mimicking the following view:

Tableau calculation mimicks this view

It doesn’t matter what is in the original view, we are telling Tableau to consider the State dimension only. Let’s add our LOD calculation to our view:

Adding LOD calculation to our view

In our Exclude example, we achieved the same result as above by excluding the City dimension. The Fixed LOD calculation is more versatile and can be used across more worksheets. That’s because it is not dependent on the dimensions used in that worksheet like Include and Exclude would be. To add another column for sum of Sales by Country, we create another calculation fixed on the Country dimension:

{FIXED [Country / Region]:SUM([Sales])}

Adding it to the view gives us the following:

Adding calculation fixed on Country Sales dimension

Success!

Let’s test this. If I added another dimension to my Include or Exclude examples, the numbers produced by the LOD calculation would change. For instance, if I added the Department dimension to my Exclude example, my view would go from this:

Before adding Department dimension to our Tableau view

To this:

After adding Department dimension to our Tableau view

Adding Department to the view has changed all of my numbers. Just to hammer it home one more time, the Include and Exclude statements are relative to the view. What happens if I do the same with my Fixed calculations? Will they change if I add another dimension, as well?

Adding another dimension to the Tableau view

No, it has not changed the values in my LOD calculations. As you can see, they remain the same even though the Sales column has changed. That’s because our Fixed calculations are not relative to anything else in the view.

One More Wrinkle

Here’s where LOD calculations get tricky. What happens if I use a completely different dimension than what I’ve used to fix my LOD calculation? Let’s use Department again. What will happen to my LOD calculation fixed on State?

Using a different dimension and the affect on LOD

The calculation LOD Sales Fixed State is not showing State-level sales. Rather, it is showing the same value that we discovered for Country/Region-level sales. Why? Is it broken?

No, our LOD calculation is working exactly as it is supposed to. Here is where LOD gets confusing. In our view, we only have Department as our level of detail. Our LOD calc is working correctly, but we haven’t provided the level of detail in the view for it to show it. Remember, we have filtered on USA data only, so it’s aggregating everything up to appear as a sum of Country/Region. In the background, it is doing this on the State level of detail. Since our aggregation type is SUM, it’s simply adding up all of the states and producing the same result as a Country/Region total.

To better illustrate this, let’s build another view. We’ll have the sum of Sales by State with a Grand Total aggregated by average:

Building another Tableau view

This view shows the sum of Sales for each state and then the average sales across all states, which comes to $176,060.67.

Remember our view that only had the Department dimension listed? We’ll change the aggregation type on each measure to an average rather than sum, and now you’ll be able to see the LOD calculation at work:

LOD calcualtion at work

Here’s how we know that our LOD calculation is working. The average sales by Department is averaged across every record in each of the three departments. That’s why those values are so small in comparison, because it’s averaging each order within those departments. The LOD Sales Fixed State calculation is looking at the sales per state level rather than on the record level. We get the same value that we had in our average across the sum of all states from the previous table. These LOD values are all the same down the column, because we have not considered the Department dimension because are fixed solely on State.

If I changed my Fixed LOD calculation to an Include calculation that included State, it would consider the sum of Sales per state but segmented by Department as well:

Changing to an Include calculation

Presto!

In our last article within our Tableau Deep Dive on LOD (Part 5), I’ll explore the difference between LOD calculations and table calculations. As always, we love to hear your comments, questions and thoughts in the space provided 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!