Making Table Calculations work in Tableau
Table calculations – easily the most complicated tool available within Tableau, and consequently the most powerful.
If you’re not familiar with Table Calculations – they are used for all the ‘tricky’ bits of analysis your boss asks you for, but doesn’t really think you’ll be able to do.
Creating a Table Calculation usually starts with a request a bit like this:
“Hey, dataguy, I have an EXCEL file here. I’ve got a KPI which shows this month’s revenue compared to the average of the previous three months – I bet you can’t work that out in Tableau!!”
This could well require the creation of a calculation which ‘moves’ – i.e. performs some calculation on data which is not in the column or row where the answer is placed, but is ‘relative’ to it.
Tough stuff if you are new to data analysis, or new to Tableau.
So here it is, a REALLY simple primer in how to make table calculations work every time.
This content doesn’t lend itself to a blog post, so we’ve had to create a packaged workbook which you can download and open in Tableau… We hope you like it, and that it can kick start your analysis in Tableau.
Get the workbook here
As always, if you need more help – please contact us @ The Information Lab.
Can you make the workbook available in Tableau Public?
Tom,
Thanks for posting this! It's the best explanation of table calcs I've ever read. This will make for a great training session for either our users internally and/or for ATUG.
I have only used the "advanced" option occasionally, but now I will definitely be using it all the time. It doesn't make any sense NOT to use it.
Andy
heyo your slides link is broken 🙂
Hi,
Thanks for letting us know about this! The link you’re referencing actually did just go to a larger version of the image, I’ve now removed it. The main link you need is the packaged workbook as this contains Tom’s great steps helping you understand Tableau’s Table Calculations.
Cheers,
Craig
Still broken. I’m getting a “redirect loop” error. 🙁
Hmm… could have been a Mediafire error… The link seems to work again now. Perhaps it could be a firewall problem your end?
You can also access the workbook here: http://public.tableausoftware.com/views/Makingtablecalculationswork_2/1
Fantastic package – I love the creative way that the tutorial has been put together inside Tableau – way to go!
I am new to Tableau and wanted to quickly ramp up and this tutorial quenched my thirst and also raised some questions to me as I could not replicate some of the examples with my current knowledge of Tableau.
In Example 1, the % of total sales axis values are showing 105% whereas if I repeat it, I get only around 20-25%. I tried to examine every field to figure out the difference, but could not. Is there any other setting that determines the value of the axis?
In Example 3, how do I get the Total bar to show up? I could guess that it is a set that is at play, and defined one. But could not figure out how to add it to the bar like the way you have done it. Can you please let me know how to do it?
In Example 1, the aim is to show how sales in the Corporate and Home Office customer segments contribute to each State’s sales.
We want the % of total sales to be calculated for each customer segment, and we want the sum of these to be 100% for each state. So we need Tableau to determine the percentage of total sales for each customer segment, treating each customer state separately.
To achieve this in Tableau:
Right-click on SUM(Sales) and select Quick Table Calculation > Percent of Total
Right-click on SUM(Sales) again and select Edit Table Calculation
This opens the dialogue window where you can configure the table calculation. Summarize the values using “Advanced…”.
This opens the advanced dialogue window where you can configure the calculation. You need to:
PARTITION your table calculation by Customer State, and
ADDRESS the calculation by Customer Segment.
This configuration will produce the desired results.
At The Information Lab, we recommend that you always expressly configure your table calculations. This is the key to producing accurate and reliable calculations.
To display the ‘total’ bar as shown in Example 3, right-click on the ‘Region’ pill (on Rows) and enable ‘Subtotals’.
I hope this (slightly verbose answer) helps!
Cheers,
Beverly
I am trying to recreate the display of the subtotal in example 3. But for some reason, the subtotal option on the menu is greyed out. Similarly, the option greys out if I uncheck ‘Subtotals’ in your example.
Apart from that, thanks for a good tutorial on table calcs!
Hey Ulrik, did you find a way around the subtotal being greyed out? Having the same problem here…
Hi Willem
There is a bug in some versions which doesn’t allow you to select subtotal. However, if you go into the Analysis menu, and into Totals, you can use “Show all subtotals” to add them.
What a great Post, it helped me to understand better table calculations
Thank you for your articles; I have carefully studied all of them over the last year and use them to prepare for my certification. Your advice is generous and much appreciated. Thank you.
I think this is the best explanation I have ever seen about table calculations!
This is really awesome. I had to write this review in the middle of going through the presentation because it’s just that helpful to me and I don’t want to forget thanking you for it! It’s much clearer than the Tableau product manual. I love the scenarios.
Thank you. This is Awesome!
This is still gold in 2016. Thank You.
Thanks for this tutorial! I’m using a newer version of Tableau in 2019 and in Example nr 1, step 2 to fix the issue “Set ‘Summarize the values from:’ to Advanced” doesn’t seem to be an option. Instead, for step 2 I clicked on “Total” under the header “compute using” in the edit table calculation pop-up window. It seems to do the trick 🙂
Hope that helps anyone who is still referencing this great tutorial!