How to Add Grand Total in Tableau

Adding grand totals to your report in Tableau seems simple, but it's not always straightforward. Thankfully, there are several ways to tackle this task, from basic one-click solutions to more complex strategies for handling intricate issues. In this guide, we'll cover everything you need to know about adding grand totals in Tableau, including customizing them and what to do when the figures seem off.

The Simplest Way to Add Grand Totals in Tableau

Tableau offers predefined options to add totals to a table or matrix right from the beginning, through the Analytics options or the pane of the same name. We will explain both, so choose the one that suits your needs. Neither option is better than the other, so don't worry!

Utilizing the "Analysis" menu

For most crosstab reports, adding totals can be done in a few clicks. Let's take the following case where we're showing Product sales by Year and Region, and want to find the total for each Year.

To do this, simply follow these steps:

1. Click on the "Analysis" menu at the top of the sheet.
2. Hover over "Totals." A menu will appear.
3. From this menu, select from several options:

By selecting "Show Row Grand Totals," "Show Column Grand Totals," or both, Tableau will add row totals, column totals, or both to your chart without any issues.

Using the Analytics pane

If you prefer a drag-and-drop approach, you can use the Analytics pane to achieve the same result. Let's use the same visualization as before.

1. Go to the Analytics pane on the left of the Tableau Workspace. If you don't see it, ensure it's activated under the "Show Panes" option.
2. From there, drag "Totals" to the view on the right. A pop-up will display your available options. In this simple case, "Row Grand Totals" and "Column Grand Totals" will appear as choices. If you have a more complicated visualization, you’ll also see "Subtotals" as an option in the Analytics window.
3. Once you add it to your visualization, the new summary column/row should appear in your table.

Troubleshooting: When Totals Don’t Appear

Sometimes, you may encounter an issue: you've tried adding totals using the method described above, but nothing appears. This is common for beginners and can be very frustrating. Fortunately, it can be resolved quickly. Let's learn why it happens and how to fix it.

Using Table Calculations

In general, the reason why the Totals feature isn't working is because you're using Table Calculations. Unfortunately, this feature doesn't work with custom aggregations created using table calculations like RUNNING_SUM, LOOKUP, or PERCENTOF. Here's how to bypass this issue:

1. Create the Table Calculation you want in the view as usual. Let's say you want to calculate the running total of product sales over 2021 and 2022 for product sub-categories.
2. If you want to include the sum of all the month's running totals at the end, the Total option will be disabled. To solve this, or to include the total for all periods, create the overall numbers on a separate sheet and then join them into a dashboard to give the impression of "Totals" in your visualization.
3. Build a new sheet, choosing the totals you want to show and filter out any unwanted data. Leave the calculation type on Automatic and name the label for the grand total. Your final summary should look something like this:
4. With both visualizations built, combine them in a dashboard to make it appear as one!

Customizing Grand Totals

Tableau offers flexibility for how you want to use the Grand Total, similar to how it offers different aggregation options for other metrics (think AVG, MIN, MAX, or SUM).

Modifying the Aggregation

Here's how to change how the grand total is calculated:

1. Click on the "Analysis" menu at the top.
2. Hover over "Totals." Click on "Total all using..."

Now you can view a list of options:

• Sum: This is the default setting. It aggregates all numbers in your rows and columns.
• Average: This calculates the average instead of a sum. Tableau will calculate the average based on all the data in the table, providing the true average.
• Max: This shows the highest value of your selected cells.
• Min: This shows the smallest value of your selected cells.

Choosing any of these will instantly change how the grand total is calculated without altering the rest of your data. This feature is useful when, for example, you want to display only a company's weekly net average for more detailed performance insights.

Renaming the "Grand Total" Label

Tableau also allows you to rename your totals to fit your needs. By changing it to terms like “Total Business Sales” or just “Total,” you improve the communication of your visualizations.

Renaming the label for the Grand Total can be done quickly by following these steps:

1. Find your Grand Total on your display. Right-click it.
2. Select the "Format..." option from the menu.
3. The Format window will open. In the Basics section, navigate to the Label shelf.
4. Type the new name, and you're done! Adjust the formatting as much as you need in this window.

A Deep Dive into How Tableau Calculates Totals

A common complaint from Tableau users is "Why are my grand totals incorrect in my Tableau worksheet?" The grand total displayed in Tableau might not be the sum of the values in the table. Many assume it’s an error on their or the software’s part, leading to frustration.

The reality, however, is that this is not a defect. Tableau is functioning as designed, even if it seems counterintuitive.

What is actually happening?

When you're trying to add up the cells in your chart to the Grand Total, Tableau takes a different approach: it recalculates using all the underlying data in the tables. This method is distinct from just "adding up what you see in the chart." By taking this approach, Tableau removes any granularity set in the views. Why does this matter?

As mentioned earlier, the true average for your dataset is calculated by averaging all data points, not just the visible rows. This is the correct mathematical approach. While it feels confusing, it is due to being used to Excel's logic, which sums what's shown in the cells, regardless of whether the underlying data is correct.

Simply put, when you add a dimension to your view, you're partitioning your measure across a table, generating cells based on a standard. In our example from sales in the years 2021 and 2022 for product sub-categories, our sales numbers are partitioned by year and sub-category. However, the grand total will work across the two dimensions and be calculated by considering all data present.

Final Thoughts

Working with grand totals in Tableau is one of the most essential skills for any desktop student to learn. From adding them with a single click in the Analysis menu to creating sophisticated solutions using various tabs to avoid software constraints, Tableau offers a comprehensive range of tools to cater to your needs.

Working with data shouldn't be complicated like this. Creating reports and extracting insights can feel like spending more time battling software tools than creating something useful. That's why we built Illustration to streamline your entire process. Connect your data sources instantly and produce advanced dashboards and reports. You can create insights in less time without struggling with tools. Why not try it and see for yourself?