Custom Table Calculations is one of the most advanced concepts in Tableau. The main resources Tableau provides can be found at On-Demand Training – Table Calculations (requires sign in) and Top 10 Tableau Table Calculations where you can find a variety of examples and a white paper with additional details. The capability of custom table calculations goes beyond the information provided in those resources, and there are many factors not covered in them that impact the evaluation of table calculations. They also have limitations, and can have better or worse performance depending on how they are written. This will be a series of posts covering concepts that will remove the guesswork and enable you to employ table calculations so you can create amazing visualizations and dynamic interactive views that apply the business logic and calculations you want.

This post will cover the difference between kinds of pills (discrete vs. continuous and dimension vs. aggregate), a worksheet layout to assist in troubleshooting your formulas, the TOTAL() function, and a basic Compute using setting. Future posts will cover topics including sorting multiple levels of dimensions (with and without a Set), including Advanced Compute using with Restarting every and at the level, the effects of a cross-tab, filtering order of operations, performance, nesting, offset, each of the table calculation functions (and their specific options in the Edit Table Calculation dialog and context menu), and maybe some other related topics as well (like data blending, custom SQL and pass-through functions). I hope to make this good resource by sharing the understanding that I have gained from trial and error, conversations with other Tableau experts and learning from their work, and experience from the Tableau Forums. If you have specific questions about how to get a result that you are looking for, I recommend starting a new thread on the Tableau forum and provide at least a sample data set.

Kinds of pills

Tom Brown of The Information Lab, wrote a great post on “Blue things and Green things”. This is an important distinction to be aware of, and the effects that Tom discusses in nice detail. I would like to build on Tom’s work, and discuss finer distinctions: Discrete vs. Continuous and Dimension vs. Aggregate. Discrete pills are Blue and Continuous pills are Green. This distinction visibly affects the display of the data and some dialog boxes.

– On the Rows and Columns shelves, Discrete Blue pills make Cells and Panes, and Continuous Green pills make an Axis.

– On the color shelf, Discrete Blue pills provide a categorical color palette picker and legend, and Continuous Green pills provide a gradient color palette picker and legend.

– On the filter shelf Discrete Blue pills provide a multi-select keep/exclude and conditional filter options, and Continuous Green pills provide range based filter options.

There are a variety of other situations where the pill being pill being Discrete or Continuous has an effect. Dimension vs. Aggregate pills provide visual clues in the text displayed in the pill, as seen in this image:

Commonly pills are Discrete Dimensions and Continuous Aggregates, but you can also have Discrete Aggregate and Continuous Dimension pills.

The combination of Dimension pills (non-aggregated Green or Blue pills) on the Rows, Columns and Marks cards defines what creates a mark in the view. Each distinct combination of values in these Dimension pills creates a mark in a pane. Dimension pills are the fields in the GROUP BY clause of the query sent to the data source.

Being aware of these Discrete vs. Continuous and Dimension vs. Aggregate distinctions is very important for understanding Table Calculations and other useful capabilities of Tableau. The Custom Table Calculation TOTAL() I have decided to start with the TOTAL() function because it is very similar to the Subtotals and Grand Totals. Also, if you have ever used the Quick Table Calculation “Percent of Total”, you have used the TOTAL() function.

Let’s start with the “Sample – Coffee Chain (Access)” data source that comes with Tableau, and place the fields “Market” and “Product” on the Rows shelf. Both these pills default to being Discrete Dimension pills (non-aggregated Blue pills). With Market on the left and Product on the right side, each distinct value in Market is creating a Pane, and each distinct combination of Market and Product is creating a Cell. Next, place the field “Sales” on the Text shelf that is on the Marks card. We now have the Sum of Sales for each distinct combination of Market and Product. We have just one mark per cell because we do not have any Dimension pills on the Marks card.

Since we have a Dimension pill on the Rows shelf, Market, that is creating panes, we can bring up that pill’s right-click context menu, and turn on Subtotals. You cannot turn on Subtotals for the right-most discrete pill. Additionally to be able to turn on Subtotals, the pill and all pills to the left need to be discrete dimensions, you can have discrete aggregates creating cells (this can happen when you have a dimension on the Marks card).

Next, from the main menu select, Table->Column Grand Totals. You can turn on Column Grand Totals as long as you do not have any Discrete Aggregation pills on the Columns shelf. Now we can see each Product’s Sales within each Market, the Total Sales for all Products within each Market, and the Total Sales for all combinations of Product and Market. To get a Percent of Total, bring up the right-click context menu for the pill SUM(Sales) on the Text Shelf, and select Quick Table Calculation->Percent of Total

Let’s investigate this formula we just created by again bringing up the right-click context menu for the pill SUM(Sales) on the Text Shelf, notice that it now has a triangle-delta symbol to note that it is a table calculation, and selecting Edit Table Calculation, and clicking the Customize button (we will come back to this dialog in more detail in another post), to get this formula:

We can break this formula down to a numerator: SUM([Sales]) and denominator: TOTAL(SUM([Sales])). Let bring those out as two separate pills. First, create a calculated field for the denominator:

and drop it onto the view using the “Show Me” drop feature that turns on the Measure Names/Values pills.

Then place the field “Sales” on the Measure Values shelf as well, and arrange as shown:

This is the worksheet layout that I prefer, Dimension pills on the Rows shelf, Measure Names on the Columns, Measure Values on the Text, and all the aggregations and table calculations on the Measure Values shelf. The result is my preferred worksheet layout for building and troubleshooting table calculations:

With this view, we can see the numerator and denominator for each cell, and see that TOTAL(SUM([Sales])) is the same value as the Grand Total (all the way at the bottom). This is because the compute using is currently set to “Table (Down)”, so it is the sum of Sales for all combinations of Market and Product. In order to get the value for the pill Total Sum of Sales to match each Market’s Subtotal, we need to set the Compute using for the pill. From Total Sum of Sales’ right-click context menu, select Compute using->Product. Perform the same setting change for the table calculation pill SUM(Sales) as well to get all the numbers to match up.

When we set the Compute using to Product, we are telling it to use Product for addressing, and all other dimension pills for partitioning. In this exact situation with the TOTAL() function and worksheet layout, I would say: We are calculating the total sum of Sales for each Market regardless of each Product, and then making that value available to each Market-Product combination. There are a variety of other ways to phrase this so that it makes sense to you. The gist of the TOTAL() function is it makes a Subtotal or Grand Total value available to all dimension combinations based on the Compute using settings.

That wraps up this post covering the different kinds of pills (discrete vs. continuous and dimension vs. aggregate), a worksheet layout that I prefer for crafting calculations, the TOTAL() function, and a simple Compute using setting. You are welcome to post your questions the topics I have covered here, and I will cover more table calculation content in upcoming posts.