This is the first video in the series – Demystifying table calculations in Tableau. In this video, I talk about the first big insight that I got about table calculations that led me to understand it much more intuitively, a feat that I could not achieve even after reading so many articles and watching so many videos.
Here is the key take away from the video:
Imagine you have a view/report that displays Total Sales by Region. Instead of displaying the raw sales numbers, you might display the percentage of sales of each region or the rank of the region, etc. This change in display is what Table Calculations are about.
To define it more specifically, Table Calculations are nothing but a way to change how a summary value like Total Sales is displayed in a view/report in Tableau.
Here is a text-version of the video for those of you who prefer to read than to watch – please note that this is NOT a transcript of the video. When I record the video, I do have a clear idea of the ideas I want to communicate, but I don’t follow a detailed written script as I just want to go with my natural flow as a teacher. Similarly, when I write after I have produced the video, I just want to go with the flow I get in writing as I try to capture the same ideas in the written form.
In this first post in the series, I intend to share with you the first big insight that I had about Table Calculations which was a turning point in my relationship with table calculations – before this insight, table calculations were hard-to-understand, not intuitive, etc. After this insight, table calculations were easy-to-understand, intuitive and useful in a lot of real-life business scenarios. I will walk through the event which led me to this insight.
But before that, let us start with the 2 standard questions everyone asks:
- What is a Table Calculation Really ? There are many different types of calculations possible in Tableau. Hence, you would want to know what distinguishes table calculations from the rest of the calculations. Here we are interested in a definition that helps to form a mental picture of what table calculations are.
- When do I use them? When you are faced with a problem where you have to create a calculation to get the results you want, how do you know whether you should use table calculations or not?
To establish the difficulty in answering these 2 simple questions, let us just visit the Tableau Desktop Manual to see how it introduces Table Calculations:
“Table calculations are computations that are applied to the values in the table. ”
This is what I would call a circular definition. Table calculation is a calculation applied to the table. How helpful is that?
“These computations are unique in that they use data from multiple rows in the database to calculate a value.”
I have 2 problem with this sentence. First, it does not shed any more light on what a table calculation is. Second, this is wrong – when you drag any measure into a view in Tableau, you get an aggregated value, which means you are bringing in multiple rows and summarizing them as a single value. Table Calculations go beyond the simple aggregation you get when you drag and drop a measure in a view.
Here are 2 other often-referred resources for understanding table calculations:
One of the Tableau Zen Masters, Jonathan Drummey, maintains a comprehensive list of resources to go through to understand table calculations. These resources are grouped by the skill levels – Beginner, Intermediate and Advanced. One of the comments in that page by another Tableau Zen Master, Shawn Wallwork, reflects the feeling the Tableau community has about table calculations:
“I just spent the day beating my head against the proverbial table calculation table. Arrrguh!”
In summary, what I want to establish is that we are not able to answer the 2 questions even after going through all these resources.
Now, let me talk about the event where I had the epiphany about Table Calculations. It happened when I looked at this Excel Menu on a pivot table. The list of calculations listed under “Show Values As” trigged a deja-vu feeling in me: “I had seen this list somewhere”.
Then I went and opened Tableau and went to the Quick Table Calculations Dialog and I saw a very similar list:
Now let us put them side-by-side so that you can see the similarity:
What Excel is making very clear is that it is ALL about changing the display. What a nice and clear name ! You are looking at a summarized number like SUM(Salary) here in a report. Instead of seeing the raw value, you right-click on the raw-value to display it in a different way : as a relative value (using Rank, etc.) or as a % of Total, as a Running Total.
How nice would it be if Tableau had a menu like this? I right-click on a value in in the table or chart and I get this menu… This simple change, I feel, could have eliminated the need for pages and pages of explanations about what table calculations are.
The big insight I had was “Table calculation is about changing the display of a summarized measure”. That means, you need to have report first with the summarized measure and then you right-click on one of those values to display it in a different form.
If you are an expert, you might object to some of my simplifications: I am purposefully giving simple concrete images so that beginners (and experts) can hold onto them for a while to get a good handle on table calculations. After a while, even beginners would not need these concrete images. And in the later modules, I would also introduce more nuances about table calculations that I am purposefully ignoring here, to keep things accessible for users at all skill levels.
I hope I have answered the first question “What really is a table calculation?”. We still have the second question “When do I use them?”. You have already seen a few examples like Running Totals, % of Total, Rank, etc. The larger question is “when you are faced with a new problem, how do you know whether to apply table calculations or not?”. Do you really need to memorize these long lists? The answer is NO. To answer this question, you need to first understand the 3 paradoxical problems in the Reporting world. Once you understand these 3 problems, you would be able to answer the question “When do I use table calculations?”.
The next post/video will introduce to the 3 paradoxical problems in the Reporting World. See you soon!