Of all the questions on the Tableau forums, one of the more common ones is that people see the Grand Total and/or Subtotal rows – the ones created by going to the Analysis Menu then choosing Totals->Row/Column Grand Totals – not providing the results that they’d expect. And then, of course they’d like to have the total rows provide the desired results. For example, the measure might be a Count Distinct or an average, and the desired total is the sum of all the returned values and not what Tableau generates.
In November 2013 Tableau 8.1 also added a new two pass totals feature that may remove the need for customizing grand totals for your use cases, more details are in this post.
This post goes through the how and why Tableau calculates the totals that it does, and then provides a couple of workarounds.
The View
We’ll start with the Superstore Sales sample data, and a basic cross tab of average sales per year per category:
As noted in the online documentation, when using the Average aggregation Tableau is first calculating the average for each row, then for the Grand Total performs the average at a higher level, i.e. the average of the averages. A longer description is to say that the Grand Total is calculated by performing the same aggregate calculation as the measure, only taking out whatever dimensions are on Rows or Columns.
Therefore if there is only one dimension like Category in the above example, then Tableau computes the AVG([Sales]) for each member of Category, then for the Grand Total computes the AVG([Sales]) for the whole dataset (having removed Category from the calculation). In the case of other aggregations, Tableau does the same: for example, for SUM([Sales]), Tableau computes the sum of sales for each member of Category, and then in the Grand Total does the SUM([Sales]) across the entire dataset.
In the case of subtotals, Tableau increases the level of aggregation by one dimension at a time and then computes the necessary subtotals for each pane.
The Problem, and Some Solutions
What if the goal is to show the total of the averages in the Grand Total instead of the average? Using the example, we’d want $6602 for the Grand Total instead of $1770. Tableau does not offer any controls for choosing a different aggregation for the Grand Total. However, like most issues in Tableau, there are a variety of workarounds:
- Use a dashboard with two worksheets: one that displays the detail rows, a second that displays the Grand Total row.
- Use a formula to identify whether the row being displayed is a detail row or a Grand Total Row, and either use that formula within a calculated field to return the desired result in each case, or use it as a flag to set a variety of calculated fields. I’ll cover the three formulas I know of in Part 2.
The Dashboard
Here’s how you create a dashboard to show a custom grand total:
- Create a worksheet with the data.
- Duplicate the worksheet to create the grand total worksheet.
- If you still need the dimension(s) in order to make the calculations work correctly, drag them from Rows and/or Columns to the Level of Detail Shelf on the Marks Card:
- Either use an existing calculated field or create a custom calculated field to generate the desired total. In this case, since we want the Grand Total to show the sum of an average, I used the following formula:
IF FIRST()==0 THEN WINDOW_SUM(AVG([Sales])) END
The IF FIRST()==0 part uses a technique developed by Richard Leeke and demonstrated in this fabulous post at Clearly and Simply on table calculations. The goal is to reduce the number of rows returned by the table calculation, so that we don’t have overlapping text.
- Create another calculated field – I called it Fake GT Header – that has “Grand Total” as the formula. Drag that onto the Rows Shelf.
- Now to set up the formatting. Right-click on the Grand Total and format the header to be Bold.
- Click on the Borders icon to set the Row Divider->Pane to None.
- Right-click on the blue YEAR(Date) pill on the Columns shelf and uncheck Show Header.
- Now the dashboard is ready to be set up. Create a new dashboard using the Dashboard->New Dashboard menu.
- Drag a Vertical Layout Container onto the dashboard from the Layout Containers section on the left.
- Drag the original data worksheet onto the dashboard from the Dashboard window on the left.
- Drag the grand total worksheet onto the dashboard from the Dashboard window on the left and place it just below the original data worksheet.
- Click on the drop-down arrow in the upper right of the grey worksheet container to uncheck Title, and you’re all set. Here’s how it looks in Tableau Desktop:
This is the approach that Tableau support recommends. It gets the job done, but has some issues:
- There’s unnecessary white space in the view that can’t be removed.
- Any filters or parameter-based calculations will need to be duplicated in both worksheets.
- Any selections in the data worksheet to exclude marks won’t be reflected on the grand total worksheet.
- If you’re using a hierarchy, expanding the hierarchy in the data worksheet will have no effect on the grand total worksheet, leading to an ugly view.
The alternative is to get more friendly with how Tableau aggregates data and computes Grand Totals, and more intimate with table calculations. If that sounds like fun to you, look for Part 2 and Part 3. And, here’s the workbook on Tableau Public:
Great explanation Jonathan. I think I know exactly where you’re headed with part 2. Joe Mako did some training/help with me last night that’s very similar.
Hi,
I am still confused how to create a calc filed to show the grand total for subtotals.
Hi, the easiest way to get a grand total for subtotals is to just turn on grand totals. I’m thinking though that you’re looking for something else (like a grand total that uses an aggregation that is different from the subtotal aggregation) and for that then I’d need more to go on. In that case I suggest creating a new post on the forums at http://community.tableau.com/ and including a Tableau packaged workbook with some sample data and a mockup describing your desired results.
Jonathan
> “Grand Total performs the average at a higher level, i.e. the average of the averages.”
An average at a different level of detail is not an average of an average.
Subtotal, Grand Total, Total Refference lines, and TOTAL() are all going to issue a seperate query to the underlying data source.
Aggregration Reference lines, and WINDOW_ functions are like a query of a query result, enabling an average of an average, or a sum of an average, or more levels.
When the pill that a Grand Total or Subtotal is appled to is a normal aggregration pill, it is effectivly wrapped in a TOTAL() function, a pill like “AVG(Sales)” becomes a formula like “TOTAL(AVG([Sales]))”. If the pill that the Grand Total or Subtotal is appled to is a table calc, then it cannot be wrapped in a TOTAL() function, and the formula is evaluated as is with the level of detail that the Grand Total or Subtotal cell is aware of.
This means that if you use a table calc, you prevent the application of the TOTAL() function, and can perform any computation that you want.
I look forward to Part 2, and hope you show the method that uses a duplicae of the dimension field on the Level of Detail to get more marks in the Grand Total.
> “Grand Total performs the average at a higher level, i.e. the average of the averages.”
*smacks forehead* I know better than that, why’d I write that? Thanks for the correction! I’ll update the post when I get a chance.
One question I have about Subtotals, Grand Totals, TOTAL(), etc. hitting the underlying data source – from looking at the logs it seems like sometimes there are separate queries issued, sometimes not. I’m guessing that there aren’t actually separate queries when Tableau has enough data to do those calculations in memory?
In part 2 I’ll show the (limited) non-duplicated dimension method and the (relatively unlimited) duplicating dimension method.
You are correct, I was just guessing that it was a separate query, but in fact, Tableau just requests additional aggregations, so if you want the AVG(Sales), with subtotals, Tableau will also request COUNT(Sales) and SUM(Sales) in order to compute the average at a different level in the Tableau data interrupter, instead of a separate query like I previously believed.
Thank you very much Jonathan, you are doing great work on this blog!
Pingback: Tableau 8.1 Two Pass Totals | Drawing with Numbers
Hola, How exactly can I replicate STep 5 “Create another calculated field – I called it Fake GT Header – that has “Grand Total” as the formula. Drag that onto the Rows Shelf”.
Thank you!
Hi Alejandro,
Here are more detailed instructions:
1) Click on Analysis->Create Calculated Field… The Calculated Field dialog will open.
2) Make the name “Fake GT Header” (without the quotes)
3) Make the fomula “Grand Total” (with the quotes)
4) Click OK to save the calculated field. Tableau will automatically make it a Dimension in the Data window.
5) Drag the “Fake GT Header” field from the Dimensions area to the Rows Shelf.
Does that answer your question?
I had what was a much simpler problem than any of those I saw in this blog, so I thought I’d share it in case it helps. My data was comprised of metrics for, among other things, number of accounts and number of accounts which voted. I had a calculated field for %accounts which voted. When I turned on the grand totals, this percentage column was added together yielding a %total greater than 100%. To fix it, I simply changed my calculation from [Voted Accounts]/[Accounts] to SUM([Voted Accounts])/SUM([Accounts]).
This is a great addition, thanks! I’m guessing that your view had record-level display in the detail area so the record-level calc was correct and only broke in the grand totals. Most people run into that issue sooner because they are aggregating in the detail area of the view, it’s good to remember that it can only show up in the Grand Totals.
Hi Jonathan, I was trying to reproduce this and the header of “Fake GT Header” is not going away in my version. What did you do to get rid of that while keeping the display of “Grand Total”.
Hi Siraj, I’m away from my computer right now, I think what you need to do is right-click on the “Fake GT Header” and choose “Hide field labels for Rows.” If that doesn’t work, post pack here and I’ll take a closer look when I can.
Hi Jonathan, Yes, your instructions worked. I was trying to find the hide field label option, but was looking at the wrong places. Thanks for your help.
i need to do grand average may i know how to do grand average in tableau i tried a lot with below formula but error is displaying.Please let me kno
IF FIRST()==0 THEN
WINDOW_AVG(SUM([Hrs#]),0,IIF(FIRST()==0,LAST(),0))
END
Hi John, check out Tableau’s two-pass totals feature http://drawingwithnumbers.artisart.org/tableau-8-1-two-pass-totals/, it’ll likely do exactly what you want without needing a custom table calc.
Also when it comes to Tableau table calc performance the “,0 IIF(FIRST()==0,LAST(),0)” is no longer necessary, Tableau fixed that problem in v8.0 or v8.1 (I can’t remember exactly).
Jonathan
I have a slightly different problem. I need a simple sum grand total, that works fine with all normal filters. However i have a parameter for display top N% of associates. When I use 10, my table will display only the top 10% of associates, perfectly, but the subtotal disregards the parameter filter and sums um the sales of ALL associates, not only the top 10%. Which route do I go?
Without more details I can’t give you an exact answer. I can think of a couple of different ways that the problem might be occurring, and depending on that there may be one or more workarounds for each. Please post some sample data and a packaged workbook with your efforts so far to the Tableau forums http://community.tableau.com or http://tableautalk.com and link back to it here, or post the workbook to Tableau Public and link back to it here, then I can take a look.
I have a crosstab with dimensions on both the X and Y axes and three measure values across the top (Dollars, Rate, Number of Records). Is there a way to show a column grand total of just the number of records of my four Y axis dimensions?
Thanks
If you are using a Measure Names/Values crosstab, there’s no way to do that in a single table because Measure Names isn’t a real dimension accessible from calculated field. Here are a couple of other options:
– Using two worksheets on a dashboard, where one has original detail and the other has just the total. This runs into problems of getting two scrollbars when there is too much data to vertically in the dashboard.
– Reshaping your data using either the Excel pivot tool, the Tableau pivot functionality built into Tableau v9, or some other method in SQL to make all your measures into a single “measure value” column with an additional “measure name” column. Then you can use a set of calculated fields to generate the right unit types for each measure that include custom Grand Total computation.
Jonathan
Hello,
My problem is simple: I have a column for which I have calculated a variance between two other columns. However, the Grand Totals display a SUM of the numbers in the column, NOT the variance between the two other Totals, as I’d like it to. Is there anything I can do?
I’m guessing that your variance measure is something like [column1]-[column2], try SUM([column1])-SUM([column2]). If that doesn’t work, then I’d at least need to see the formula along with a screenshot of your entire view, including all Shelves and the Marks Card.
Okay now lets imagine if we were going to carryout a row grand total and a column grand total . and in the row grand total we have to sum and in the column grand total we have to carry out an average . how can this be addressed .
Hi,
I’ll have something for this in a couple of days…in the meantime, what version of Tableau are you on?
Hey Jonathan ,
I’m currently on 8.3.
Thanks
I just posted http://drawingwithnumbers.artisart.org/lod-expressions-and-separate-custom-grand-totals-for-rows-and-columns/, there’s instructions there on how to do it in Tableau v8 as well as a (what I believe is simpler) alternative for v9.
Pingback: LOD Expressions and Separate Custom Grand Totals for Rows and Columns | Drawing with Numbers
is this concept what you say is seen in new version also i mean 10.0 ?
because i have data like this, i i see the avg sales it is correct in grand totals ?
XXX 100 7/4/2011
YYY 200 7/3/2012
CCC 300 7/2/2010
XXX 100 8/4/2010
YYY 200 8/3/2011
CCC 400 8/2/2011
XXX 100 8/4/2012
YYY 200 8/3/2010
CCC 500 8/2/2012
result for avg sales
orderdate orderdate orderdate
Sno 2010 2011 2012
CCC 300.0 400.0 500.0
XXX 100.0 100.0 100.0
YYY 200.0 200.0 200.0
Grand Total 200.0 233.3 266.7
Hi Arya, there’s no new behavior for grand totals in Tableau v10.0, what you describe is what I’d expect to see with the measure as AVG(Sales) and the default Total using->Automatic. Does that answer your question?
still did not get you on this one
for this data
Order Date Order Date Order Date Order Date
Product Category 2010 2011 2012 2013
Furniture 5,973 9,428 10,844 2,706
Office Supplies 2,836 422 632 3,423
Technology 254 4,362 2,392 2,695
Grand Total 2,910 3,619 3,273 3,160
avg (sales) for last column is showing 3160 , but 2706+3423+2695 =8824/3 =2941 but showing as 3160
and for this data
esult for avg sales
orderdate orderdate orderdate
Sno 2010 2011 2012
CCC 300.0 400.0 500.0
XXX 100.0 100.0 100.0
YYY 200.0 200.0 200.0
Grand Total 200.0 233.3 266.7
last column : 500+100+200=800 and 800/3 =266.7
same scenarion but grand totals are different ?
and please explain clearly about this
“what you describe is what I’d expect to see with the measure as AVG(Sales) and the default Total using->Automatic. “
Hi, I’m responding to this one and including responses to your other questions because it’s go the best examples. There are multiple levels of detail (aka grains of data) that we work with in Tableau, I’ll first walk through an example using your data, then Superstore.
Now let’s talk about Superstore in this same context:
To try to be absolutely clear, for each year there are 4 different AVG(Sales) being computed: There are three AVG(Sales) results covering the three Product Categories, and then a fourth AVG(Sales) that is the AVG(Sales) at the level of Year in the Grand Total.
So Tableau’s default grand total behavior (called Automatic in the user interface) is *not* “sum up the visible rows and divide by the number of rows” as you described in the calculation 2706+3423+2695 =8824/3 =2941, instead we can describe Tableau’s grand total behavior in a couple of ways: 1) the grand totals are properly weighting the measure based on the underlying data, or 2) the grand totals are re-calculating the measure at the (coarser) grain of the particular grand total.
The “sum up the visible rows and divide by the number of rows” method is called two-pass totals in Tableau and was introduced in v8.1, see http://drawingwithnumbers.artisart.org/tableau-8-1-two-pass-totals/ for more details on that.
Does that answer your questions?
Jonathan
please share the excel sheet which you have used to suggest
Hi Arya, I don’t know what Excel sheet you are referring to??
i mean the example you have mentioned , which superstore data you used , because there are different version of superstore data
You can download the Tableau Public workbook to use the same version of superstore.
can you explain here , i am using grand totals and avg (sales) ?
Order Date Order Date Order Date Order Date
Product Category 2010 2011 2012 2013
Furniture 5,973 9,428 10,844 2,706
Office Supplies 2,836 422 632 3,423
Technology 254 4,362 2,392 2,695
Grand Total 2,910 3,619 3,273 3,160
how 2910 is calculated , what is wrong here ?
and also what is exactly meaning of this one ?
when using the Average aggregation Tableau is first calculating the average for each row, then for the Grand Total performs the average at a higher level,
“performs the average at a higher level,” ?
Hi All,
Can anyone please tell me how do we perform “Fixed” LOD expression in 8.3?
Thanks,
Kiran
Hi, LOD expressions weren’t introduced until v9.0. In 8.3 and earlier we got the (roughly) equivalent results using data blending, table calculations, and/or custom SQL/custom queries/views.
Jonathan
Thank you Jonathan for your help and insight on this! Your explanations and examples are a great help.
Have you encounter or know how to customize grand total percentages? For example, I want to get the grand total “current on hand unit count” column divided by the grand total “max capacity for on hand unit count” column to get the capacity percentage= “Utilization %”. I hope this makes sense.
Hi, there are multiple ways I can interpret your question, could you put together a sample workbook and put it up on Tableau Public or somewhere and give me a link and I can take a look?
I’ve created a sample here: https://public.tableau.com/profile/sheng5683#!/vizhome/Utilizatio_TestGrandTotal/Sheet1?publish=yes
The Grand Total for “Utilization%” is currently showing 99% but it should be 96% (255/265= 96%). Not sure how to customize the grand total for Utilization% or if there’s a way to show it in the Grand Total.
You can right-click to bring up the context menu for the pill “AGG(Utilization%)”, and under “Total Using” change the option to “Automatic”. The option for “Average” overrides the expression in the Totals sections and instead performs an average of the four percentages.
Thanks, Joe!
Hi Joe. Thank you for the help. When I change the “Total Using” for “AGG(Utilization%)” to “Automatic” it gives me 385%, which isn’t right. It should be 96%.
Is there another way to get the Grand Total for “AGG(Utilization%)” to calculate SUM(On Hand Unit)/ Avg(Max On Hand Unit per Building)?
Do you think because I’m using Utilization%=SUM([On Hand Unit])/AVG([Max On Hand Unit per Building])? Maybe I need to change AVG([Max On Hand Unit per Building]) to a total average sum. I’ll test this out and see if it works.