Advanced Calculations in Tableau

Table Calculations are awesome, underused, and while it might take a little time to understand the concepts, with some practice, they will help you solve many challenges in Tableau

In this section, we are going to explore Table Calculations and in the next section we will go through several exercises together

Tableau’s mission is to help people see and understand data, and as such, Tableau has given us Quick Table Calculations

We will use these Quick Table Calculations to start our exploration of Table Calculations

While we can control Quick Table Calculations using the Compute Using, we can provide further configuration using Edit Table Calculations

Quick Table Calculations are great for getting us started, however, to truly understand Table Calculations, we will need to look at writing our own Table Calculations

While we can control Quick Table Calculations using the Compute Using, we can provide further configuration using Edit Table Calculations

Table Calculations can contain other Table Calculations, in essence, they become Nested; this is extremely powerful but can be a little tricky

Table Calculations can be used to filter your reports, and as Table Calculations are computed after a Table has rendered, they are Last Filters to be applied

In this section, we explored Table Calculations and hopefully, you have a good understanding of how they work

In this exercise, we are going to look at writing a Table Calculation to create a Cumulative Sum, to do this, we will leverage the RUNNING_SUM function

In this exercise, we are going to look at writing a Table Calculation that computes the Percentage of Total

In this exercise, we are going to look at writing a Table Calculation to Rank our Dimensions based on an aggregation, we will look at the RANK functions

In this exercise, we are going to look at writing a Table Calculation that returns a Moving Average.

Note: this is extremely useful when we are looking at trends over time as we will smooth out our curve

In this exercise, we are going to look at writing a Table Calculation to calculate the difference between two values in a Table

In this exercise, we are going to look at writing a Table Calculation to retrieve the first value within our Table and then make all values relative to this, in essence, we are creating an Anchor value

Note: you can see this with COVID Google Mobility, we do not see the numbers, but the relative change between today and a point in time

Level-of-Detail (LOD) Expressions were introduced to give us control of how aggregations behave when added to a view

In this section, we are going to explore Level-of-Detail (LOD) Expressions and in the next section we will go through several exercises together

Before we dive into Level-of-Detail (LOD) Expressions, we need a quick recap as to how Tableau Aggregates your Data, in essence, the first View Level-of-Detail

When Tableau introduced Level-of-Detail (LOD) Expressions, they added three Scoping Keywords: FIXED, INCLUDE and EXCLUDE, as well as a new Syntax { SCOPING [DIMENSIONS] : [AGGREGATION] }

The FIXED Scoping Keyword allows us to define what Dimensions will be used in our Calculations Fields, therefore, this makes our calculation independent of what Dimensions are in the View Level-of-Detail

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

The INCLUDE Scoping Keyword allows us to include additional Dimensions to our aggregated Calculations, in essence, we are adding Dimensions to those that are in the View Level-of-Detail

{ INCLUDE [Region] : SUM([Sales]) }

The EXCLUDE Scoping Keyword allows us to define what Dimensions to be excluded when we aggregate our data, in essence, we are removing Dimensions from those that are in the View Level-of-Detail

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

The result of a Level-of-Detail (LOD) Expressions can be Nested; the nesting can happen in a single Calculated Field or by referencing multiple Level-of-Detail (LOD) Expressions.

{ EXCLUDE [Order Date] : AVG( { INCLUDE [Category] : AVG([Sales]) } ) }

Level-of-Detail (LOD) Expressions have different filtering order depending on the type of expression that we have written

When using Level-of-Detail (LOD) Expressions, there are several things to be aware of

In this section, we explored Level-of-Detail (LOD) Expressions and hopefully, you have a good understanding of how they work

In this exercise, we will look at what happens when you do not have a Scoping Keyword, note that this is useful, and I use this regularly

In this exercise, we will write a FIXED Level-of-Detail (LOD) Expression to help us create a Customer Order Frequency

In this exercise, we will write a FIXED Level-of-Detail (LOD) Expression to help us create a Cohort Analysis

In this exercise, we will write an INCLUDE Level-of-Detail (LOD) Expression to help us create an Avg. Sales per Customer

In this exercise, we will write an INCLUDE Level-of-Detail (LOD) Expression to help us explore Average Daily Sales, but to visualise on a monthly basis

In this exercise, we will write an EXCLUDE Level-of-Detail (LOD) Expression to help us remove Dimensions from our Calculations

Where there is a beginning there is an end, and I am thankful that you have made it this far, and hopefully, you have learned a lot from this course. We are going to review the entire course and talk about our journey.

Given you have come all this way and completed the course, you will be in a better place to understand how Advanced Table Calculations work, and hopefully, they will spark off your imagination.

While I have trained thousands of people in person, I hope that you have enjoyed it and I do hope that you stay connected and let me know about your progress.

Do reach out and connect with me on my various platforms.