Working with Window Calculations and TOTAL() in Tableau

Today, we will talk about Window Calculations and the TOTAL() function in Tableau.  Window Calculations allow you to implement a second level of aggregation on top of your basic aggregations, such as SUM() and COUNT().  This post is the ancestor to Working with Table Calculations in Tableau.  If you are not familiar with Table Calculation and "Compute Using", you should read that post first.  This is also the first post where I am using Tableau 8.0.  As usual, we will use the Superstore Sales sample data set in Tableau.

First, let's look at the TOTAL() function.  The TOTAL() function applies the underlying aggregation to the entire pane instead of each individual row.

Step 1:

• Create a text table with two hierarchical dimensions on the rows

Sales by Sub-Category

Step 2:

• Calculate the total sales across all categories
• Calculate the total sales for each category
• Create the following calculated fields

Category Total

Overall Total

Take care to notice what values are being summarized over.

Step 3:

• Add these values to the table

Sales by Sub-Category (with Totals)

This example is somewhat silly because this is much better accomplished by adding Row Subtotals.  However, this is a great example to show how the TOTAL() function works.  

Now, let's move on to Window Calculations.  We will use WINDOW_SUM(); however, the same techniques can be applied for the other Window Calculations as well.  A Window Calculation requires three input parameters.  These parameters are an aggregated value, a displacement value for the starting point, and a displacement value for the ending point.  For example, 

WINDOW_SUM( SUM( [Sales] ), 0, 0 )

will calculate the SUM of SUM of [Sales] for the current row only, which is just the SUM of [Sales]; whereas

WINDOW_SUM( SUM( [Sales] ), FIRST(), LAST() ) or just WINDOW_SUM( SUM( [Sales] ) )

will calculate the SUM of SUM of [Sales] for the entire pane, which is identical to using

TOTAL( SUM( [Sales] ) ).

Step 1:

• Create a text table

Sales by Sub-Category

Step 2:

• Calculate the sum of each Sub-Category along with the previous and next Sub-Category
• Create the following calculated field

Sum of Neighbors

Step 3:

• Add this value to the table

Sales by Sub-Category (with Sum of Neighbors)

Now, you can see that each value is the Sum of [Sales] for this Sub-Category as well as the two neighboring Sub-Categories.  Care should be taken to avoid situations such as the first as last row, where there is no previous or next value, respectively.

There is so much more to be done here.  Try combining Window Calculations with different aggregrations, such as Sums of Averages, or try creating your own Percent of Total calculations.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn