A quickie on nested table calculations

When speaking about table calculations a repeated request is to show ranking over time based on for example cumulative sales.

Usually, the question will be why what the asker is doing does not work as intended. What can be seen is usually this

//NOT WORKING
RANK( RUNNING_SUM( [SALES] ) ) )

The common complaint is that this does not yield what the asker wants to see, no matter how the calculation is set to be computed.

The reason lies in how Tableau considers the table calculations. In the above case, this is considered only one table calculation and therefore, we can only set one calculation direction.

However, what we need is the ability to set two directions. One for the inner part, i.e. the running_sum of sales and one for the outer part, the rank.

In order to achieve that, simply create the calculation in two steps:

//CUMULATIVE SALES
RUNNING_SUM( [SALES] )

Now we can wrap this calculation into another calculation

//Rank by cumulative sales
RANK( [CUMULATIVE SALES] )

Now we have a true nested table calculation and are able to set both calculation directions.

Here is the result based on sample – superstore data. See how the ranking moves depending on what the cumulative sales value is.

And that’s it for today.

As always I hope this was useful and I appreciate your feedback.

Until next time

Steffen