Tableau 101: Table Calculations, Part II

Today, on Tableau 101 let’s talk about how table calculations are among the most powerful features Tableau has to offer for answering your analytical questions. You can select from a collection of pre-defined calculations or create your own Tableau Table Calculations from scratch using Table Calculation functions.

In our last blog post, we used quick table calculations to find the most popular national park in the US. This process was simple and allowed us to find answers quickly. We used this example to explain addressing and partitioning, key parts of computing any table calculation. Now we can continue with the rest of our questions:

• Which park is most popular? (Part I)
• Has popularity of national parks grown over time? (Part II)
• Which park is most popular within each region? (Part II)

We will use Advanced Table Calculations to make both our questions and answers more precise.

Level 201 – Advanced Table Calculations

Advanced Table Calculations build on the Quick Table Calculations I covered in Part I of this blog series. Advanced Table Calculations allow you more options to modify and customize your calculation.

In Part 1, we looked at which national park is the most popular. I wanted to learn more about national parks so here we will answer questions 2 and 3 above.

First, set up your view and add a simple Quick Table Calculation:

• Drag Year to Columns.
• Drag Visitors to Rows.
• Drag Visitors to Rows again so that you see Sum(Visitors) twice.
• Right-click on the second Sum(Visitors), choose Quick Table Calculation, and choose Moving Average.
• Right-click on Sum(Visitors) and select Dual Axis.
• Right-click on the second axis and select Synchronize Axis.

Note: For this example, I filtered the view by Year (2012-2016) and Unit Type (National Parks and National Historical Park and Preserve), then excluded Denali National Preserve because it held the same data as Denali National Park

Editing Your Table Calculation

This is where you can transform your quick table calculation into an advanced table calculation.

To edit your table calculation:

• Right-click Sum(Visitors) and select Edit Table Calculation.
• Use the following settings:
• Calculation Type: Moving Calculation
• Summarize values using: Average
• Previous values: 10
• Next values: 0
• Current value: checked on

Editing our calculation and changing the previous values to 10 will smooth out the Moving Average so we can see the overall trend. We can see from the graph that the popularity of national parks has increased steadily over time, with a steep rise starting around 1945. We might also consider that over time more National Parks opened which could contribute to the increase in these numbers.

Further Customization Options:

At the Level – this option is only available when you select Specific Dimensions and when you have two or more addressing dimensions. At the Level can be used to ignore dimensions in the table calculation. This is particularly useful when you have fields in your view that you don’t want to include in your calculation, for example, if you’re showing year, quarter, month, and day but you want the calculation to be performed at the year level. The dimension used for At the Level (e.g. year) is used for addressing and all other addressing fields (quarter, month, day) are ignored without becoming partitioning fields.

It is also important to note that the order of the dimensions in the At the Level window determines the order for sorting with the top dimension being the primary sort field.

Restarting every – this option lets you explicitly set what dimension will control the partition (i.e. when the calculation restarts). Normally, Tableau will partition using any dimensions in the level of detail of a view that are not designated as addressing fields.

Our example from above uses only one dimension, so At the Level options are unavailable. Let’s try another example.

First, set up your view:

• Drag Sum(Visitors) to Columns.
• Drag Region, State, and Unit Name to Rows.
• Create a calculated field called Rank that contains: Rank(Sum(Visitors)).
• Right-click Rank and select Convert to Discrete.
• Drag Rank to rows.

Customize your table calculation:

• Right-click on Rank and select Edit Table Calculation.
• Use the Specific Dimensions option to select Region, State, and Unit Name. Note: Be aware of the order of these fields as this will affect the calculation.
• Use the menu dropdown for At the level and select Deepest (uses the level of granularity defined by the dimensions in the view level of detail).
• Use the menu dropdown for Restarting every and select Region.

Notice that our view level of detail includes region, state and unit name, but we are just looking for a ranking of the regions. Before customizing our view, our Rank function was based on Unit Name at the most granular level. By using At the level (deepest) and Restarting every (region) our Rank function is now based on Region.

Before Customization – Rank using Table(down) – rank of unit names:

After Customization – Rank using Specific Dimensions and ‘At the level’ – ranks parks within each region:

Rank using ‘At the level’ with different order of dimensions – ranks parks within each state:

You can also use Relative To and Sort Order to further specify how your calculation is being computed. These options are in the same location as At the level in the Table Calculation Dialog Box but are only available for certain calculations (neither are available for Rank).

It’s easy to remove a table calculation if you decided you want something different.

Removing a Table Calculation:

• Right-click the measure with the table calculation applied to it and select Clear Table Calculation.
• Tableau removes the table calculation and shows the values of the original measure.

Level 301 – Custom Table Calculations

Custom Table calculations are created by saving an existing table calculation to a calculated field or by writing the table calculation from scratch. With custom table calculations you can create complicated calculations including nested table calculations.

Note: Some table calculations, such as Window Average, can only be created using the calculated field approach.

For this example, we will address the question of which park is the most popular within each region directly.

Option 1: Convert to Custom Calculation – This is an option if you have already created a quick or advanced table calculation:

• Drag SUM(Visitors) with your table calculation applied from the Marks card to the Data pane.
• Name your calculated field.
• Open the saved calculation in the Calculated Field Editor to edit it.

Option 2: Create Custom Table Calculation – This is where you create a table calculation completely from scratch:

• Drag Visitors to Columns.
• Drag Region and Unit Name to Rows.
• Right-click on the empty space in data pane and select Create Calculated Field.
• Name your calculation Max per Region and set Sum(Visitors) = Window Max (Sum(Visitors)).
• Drag Max per Region to Color on the Marks card.

This visualization uses the custom table calculation to highlight the most popular National Park within each region in orange.

Editing Custom Table Calculations

• Right-click on measure and select Edit.
• In the dialog box click on Default Table Calculation.
• Use the menu dropdown for Computer Using, At the level, and Restarting every.

Conclusion

Although table calculations may be difficult to grasp, they are a powerful tool in Tableau and learning the basics of these calculations can help you make powerful transformations to your data. After spending some time working through these table calculations, I’m ready for my next adventure: spring

This list has me thinking about parks that I want to visit and knowing which parks have the most visits is one angle as to where I should go next and working with this data has me excited thinking about what parks to visit in the future. I’m already thinking about planning my next trip and I’m torn between spending a week in North Carolina in the Great Smokey’s as the top-ranked National Park, or traveling out west and hitting a couple other highly-ranked parks like the Grand Canyon and the Rockies. Anybody have any ideas where I should go?

Whether you choose to stay simple with a quick table calculation or just customize your calculation further, there are many possibilities to choose from.

Click here to see the solution for the steps we went through in this blog post.

Want to see how you can take your Tableau projects to the next level? Learn more about how our experts can help grow your skills today.