In Part 1 of this series, I introduced one workaround to the issue of getting Grand Totals to show a different value from the Tableau defaults, by using two worksheets – one for the detail rows and one for the Grand Total row – on a dashboard. That method has a few limitations, the biggest being that it can’t handle Subtotals. Here, you’ll learn two additional techniques for customizing Grand Totals and Subtotals in a single worksheet, and their limitations: using MIN() and MAX() to test for the Grand Total row, and using a table calculation with a duplicated dimension.

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 Tableau 8.1 Two Pass Totals.

Thanks to Joe Mako for his support in creating this, he provided the seeds for starting my own explorations into Grand Totals, and also feedback on this post. If you want to boost your Tableau knowledge, sign up for the forums, go to Joe’s user page, and start reading posts he’s has contributed to, there are over 3,500 of them!

## The Goal

For this example, the goal is to get the Grand Total row to show a sum of the average sales per product category, instead of the overall average of the data set, which is the Superstore Sales sample data:

## How Grand Totals Work

Tableau does not specifically compute Grand Totals and Subtotals as an aggregation of what is displayed; instead, Tableau is performing the same calculation as the measure, but at a higher level of aggregation. For Grand Totals for a column, Tableau takes out all the dimensions on the Rows Shelf and then performs the calculation. For Grand Totals for a row, Tableau takes out all the dimensions on the Columns Shelf and then performs the calculation. In essence, Tableau is combining two different views of the data set to create the final worksheet, as in this example using AVG(Sales):

Tableau first computes the AVG(Sales) for each Category, then for the Grand Total row it takes Category out and computes AVG(Sales) across the entire data set. You can see what Tableau displays in the Grand Total row for each of the aggregations in the online help. On a more technical level, Tableau attempts to compute the Grand Total from the in-memory data cache. If there’s not enough information available, Tableau revises the query to the data source to include additional fields so Tableau can compute the Grand Total. For example, for AVG(Sales), Tableau will add COUNT(Sales) and SUM(Sales) to the query. You can see this the Tableau log files located in the …\My Tableau Repository\Logs directory.

If we want to gain control over the value for the Grand Total, we have to do the following:

- Identify how and when Tableau is computing the grand total row
- Create the measure that returns the desired value for the Grand Total rows and another value for the data rows. This post offers two different calculations, one using MIN() and MAX() and the second using a table calculation with a duplicated dimension.

## Identifying the Grand Total Row

The key to working with the Grand Total row is to consider the dimension(s) that is/are available to the Grand Total computation, as compared to what is available to the computations for the detail rows. In the graphic above, we can see that for the detail rows that there is a row for each Category is being used, whereas in the Grand Total row, there is no Category because it’s been removed from the overall level of detail in the query. Therefore, all members of the Category dimension will be available within query that makes up the Grand Total row.

If that’s confusing, here’s an example to help you out: Here are two views, one with Category and ATTR(Category) on the Rows Shelf, the second with just ATTR(Category) on the Rows Shelf. ATTR() is an aggregation that returns a value for the specified field if there is one and only one value within the partition, otherwise it returns Null (which is displayed as an asterisk “*”). On the top view, you can see that ATTR(Category) returns the correct Category value when Category is on the Rows Shelf because there is only one Category being returned, but ATTR(Category) displays * when Category is no longer a dimension in the view, because all the values of Category are being returned.

These two views duplicate what is happening inside of Tableau when it’s computing a Grand Total row.

## Customizing Grand Totals with MIN() and MAX()

We can take advantage of the fact that the dimension(s) used in generating the Detail rows are not used in the query that generates the Grand Total row. In the Grand Total query, all the values of Category – Furniture, Office Supplies, and Technology – are being returned, and Furniture != Technology, so we can use the following formula to identify the Grand Total row:

MIN([Category]) != MAX([Category])

Here’s that test in a calculated field, showing one common request, for the Grand Total to show the sum of all sales while the detail rows show the average of sales:

IF MIN([Category]) != MAX([Category]) THEN //Grand Total result SUM([Sales]) ELSE //detail row result AVG([Sales]) END

You can also filter data within the Grand Total Row, such as if we wanted the Grand Total row to only show results for certain values of Category like Technology:

IF MIN([Category]) != MAX([Category]) THEN AVG(IF ([Category] = "Technology" THEN [Sales] END) ELSE AVG([Sales]) END

Or maybe you just don’t want the Grand Total row to show anything for certain columns in a crosstab, so the evaluation could return Null for the Grand Total row and the desired aggregate for the detail rows:

IF MIN([Category]) != MAX([Category]) THEN Null ELSE AVG([Sales]) END

This last calc could be simplified even further, but for ease of maintenance I like to be explicit about what the calculation is returning.

#### Using MIN() and MAX() with Text Tables with Measure Names and Measure Values

You can use a whole set of these calculations in a text table using Measure Names and Measure Values in the same view:

#### Custom Subtotals using MIN() and MAX()

In order to use this with Subtotals, the dimension used for testing the MIN() and MAX() needs to be the one with the right-most pill on the Rows Shelf (for column Grand Totals) or Columns Shelf (for Row Grand totals). For example, in the following view the original test on Category fails in the Subtotals but works in the Grand Total, while the test on Container works for both the Subtotal and Grand Total rows:

#### Limitations of the MIN() and MAX() Technique

This technique has the following limitations:

- Given that testing for MIN() and MAX() explicitly identifies a dimension, if a user dynamically expands or collapses a hierarchy then this calculation can fail. There are a couple of workarounds: One is to use parameters and calculated fields to control the dimensions in the view and the calculations – see this post at Clearly and Simply for the basics, a second is to use parameters to swap out different views that would be at different levels in the hierarchy – see this post on the Tableau forums for instructions, and a third would be to use a calculated field with nested tests on the different dimensions.
- When using subtotals, if the bottom level of a subtotal dimension has only one member, then the test will fail, since MIN() and MAX() will return the same result. The solution to this is to use Custom SQL, which will be covered in Part 3 of this series.
- This test can only return an aggregate result. The goal for this example is to return the sum of the average of Sales per Category, in other words an aggregate of an aggregate, and in Tableau that means we’ll need to use a table calculation and a different method of computing the Grand Total.

## Customizing Grand Totals with Table Calculations

The desired Grand Total result of $6716 is an aggregation (sum) of an aggregation (avg) that is performed in Tableau, so that requires a table calculation:

WINDOW_SUM(AVG([Sales]))

The AVG([Sales]) requires Category to be in the view so the average can be generated, as in the following view:

The default Compute Using of Table (Down) creates the $6716 result for each detail row for Category, but the Grand Total row is showing $1776. The same occurs when the Compute Using is set to Category. This is a big clue, but there’s a problem: In order to get the Grand Total calc to work, Category needs to be in the overall level of detail at the time Tableau is computing the Grand Total; however, Tableau is removing Category from the view to compute the Grand Total. It’s a conundrum.

#### Cracking the Category Catch-22

The solution to the problem of Category not being available in the overall level of detail is incredibly simple, while taking one more step to clean up. Duplicate the dimension, and put the duplicate on the Level of Detail Shelf, as in the following view that uses the WINDOW_SUM(AVG([Sales])), with the Compute Using set to Category (copy).

This looks really good, though it’s not quite there:

- What’s happening in the detail rows (the ones for each Category) is that the calculation is being performed for each value of Category (copy), i.e. the Compute Using or addressing, with a new calculation happening for each new value of Category, i.e. the partitioning. Since Category and Category (copy) share the same values, the calculation is performed once for each value and the WINDOW_SUM(AVG([Sales])) just returns the AVG([Sales]).
- For the Grand Total row, Category is removed from the overall level of detail. So the calculation is performed for each value of Category (copy), i.e. the AVG([Sales]) is calculated for each value of Category (copy) and then all the results are summed together. Only this process happens 3 times, once for each value of Category (copy), and we end up with overlapping text in the Grand Total row that looks bold, but really isn’t, since Grand Total rows aren’t bold, as we can see in the initial view:

To fix this, we turn to Richard Leeke’s work on improving table calc performance and reducing the # of rows returned, to create the following formula:

IF FIRST()==0 THEN WINDOW_SUM(AVG([Sales]),0,IIF(FIRST()==0,LAST(),0)) END

Success!

Here’s what’s happening under the hood:

#### Using Table Calcs with Text Tables with Measure Names and Measure Values

When using this technique, if you drag a regular aggregate measure such as SUM([Sales]) into the view, Tableau displays overlapping text for the total because of the copied dimension being in the level of detail:

To fix this, each and every measure needs to be wrapped in a calculated field that has the Compute Using set to the dimension (copy), as in:

IF FIRST()==0 THEN WINDOW_SUM(SUM([Sales]),0,IIF(FIRST()==0,LAST(),0)) END

Then you can have something like this:

#### Custom Subtotals Using Table Calcs

In order to get the subtotals to work at the custom level, you need to add a copy of each dimension to the Level of Detail, then for the calculation set an Advanced… Compute Using that includes all of the copies:

#### Limitations of the Table Calculation Technique

Using table calculations to generate the custom grand total has the following limitations:

- Just like the MIN() and MAX() technique, the calculation will fail when using subtotals and there is a single value for a dimension in the subtotal. The solution to this is to use Custom SQL, which will be covered in Part 3 of this series.
- Because this technique uses a special Compute Using to generate the totals instead of testing for the Grand Total row, it is not possible to have the custom sub-aggregations like the MIN() and MAX() technique. One workaround is to use Custom SQL, another it to use a more complex table calculation that I might cover later, since there are only rare use cases.

## Conclusion

Hopefully, you learned more about ATTR() and aggregation in Tableau, how to remove overlapping text, and, of course, how to generate your own custom values for Grand Totals and Subtotals using two different techniques: using MIN() and MAX(), and a table calculation with a duplicated dimension.

Part 3 of this series describes how to use Custom SQL to have full control over your Grand Totals and how to display text in the Grand Total row.

Joe MakoThis is a fantastic series, thank you so much for putting all the great content together.

One small detail, is in the two sentences where your say “For Grand Totals for a column, Tableau takes out all the dimensions on the Rows Shelf and then performs the calculation.” I believe it would more accurate to say “discrete pills” in place of “dimensions”, as you can have continuous dimension pills that will still be respected in the Grand Total.

Ben SullinsIt looks like the link to the ‘Part 1’ is incorrect. I believe it should be this:

http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/

Jonathan DrummeyPost authorHi Ben,

Thanks for the catch, it should be fixed now.

Pingback: Drawing with Numbers | Customizing Grand Totals – Part 1

Cathy BridgesWow – this was a lifesaver for me, and it also demonstrates the necessity for Tableau to make these kinds of calculations much simpler!! Thanks for all the hard work that is making my life easier!!

Jonathan DrummeyPost authorYou’re welcome! BTW, there are a few Ideas for improving totals at http://community.tableausoftware.com/community/ideas, if you’re not already registered for the Tableau Community Forums please do so and vote them up!

Cathy BridgesThanks! I recently discovered the ideas site and have been adding my 2 cents here and there! It’s nice to have a workable option in the mean time. I know there have been some discussions regarding the value of data tables in Tableau, but in my business, when we’re transitioning from Excel to Tableau and presenting new formats to a lot of left-brainers, we really need to incorporate data with the visualizations. Seriously, you’ve saved me many headaches!!

Chad KDoes this work if you want the user to be able to have collapsible levels of detail? ie: Country, State, City, Zipcode?

Joe MakoChad, it depends on the exact situation, what computations, interaction, and behaviour you want. I am sure a solution can be created for you situation, but it may not be the exact route that you expect. If you would like to explore some options, please post a new question on the Tableau form, attaching a packaged (.twbx) with sample data that represents your situation, and reply with the link here.

Jonathan DrummeyPost authorHi Chad,

What Joe said is the best bet. If you are using Tableau hierarchy then different techniques can be used, but which one is dependent on what your calculations are, the complexity of the view, etc.

yogesh kumarhi sir

please help me.

this exp. in excel.

2012 2013 Growth result in per.

45 43 =(43-45)/43

how to get same result in single column with per mark in tableau.

i am trying to do this but not getting the result.

Jonathan DrummeyPost authorPlease post a packaged workbook with some sample data to the Tableau Community Forums, then post a link to that back here and I’ll take a look.

Pingback: 100,000 Hits and an Announcement | Drawing with Numbers

Pingback: Customizing Grand Totals in Tableau v8 – The Stacking Snag | Drawing with Numbers

Pingback: Learning something new every day: Annotating Subtotals | Drawing with Numbers

PradeepJonathan, can you please provide the Tableau workbook for the methods in Part 2 ?

Jonathan DrummeyPost authorI’m sorry, I don’t have a workbook handy (this was an early blog post and I don’t have full archives of all my source material for those). I’d recommend doing a search on the Tableau forums at http://community.tableausoftware.com/, if you search for http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2 you will find a ton of examples of the different techniques. Every time I reply on the Tableau forums and use one of my grand totals posts as a reference, I link back to here so there’s a good list of use cases.

Siraj SamsudeenHi Jonathan, if you are OK with it, I can produce the workbook for grand totals part 2. I took your part 1 file and have reproduced the formulas myself to see how they are working. If you allow me, I can clean it up and post it so that all the field names reflect what you have shown on the screenshot – this might help beginners who might need to look at the formulas to understand what is going on. Please let me know if you want me to do this and I can send you the workbook file.

Jonathan DrummeyPost authorThat would be much appreciated, thanks, Siraj!

Siraj SamsudeenHi Jonathan,

You said: “ATTR() is an aggregation that returns a value for the specified field if there is one and only one value within the partition, otherwise it returns Null (which is displayed as an asterisk “*”).”

Does this not mean that we can test for NULL value being returned at the grand total column? At least theoretically, this will solve the second limitation of the MIN and MAX comparison test. I tested this by using ISNULL(ATTR(Category)) but this test fails at the grand total level because it returns nothing whereas at the detail rows level, it returns false. I wonder why it is not returning TRUE at the grand total level.

Jonathan DrummeyPost authorHi Siraj,

My writeup was incomplete: Technically the asterisk * returned by ATTR() is a special variation on Null. This causes ISNULL(ATTR([Category])) to return False when ATTR() returns *. I did a writeup on testing ATTR() here: http://community.tableausoftware.com/docs/DOC-1355.

I don’t see how testing for ATTR() returning asterisk or not would help in the single member dimension & subtotal case (and I’d love to be proven wrong). The issue I’ve always run into is that in the situation where the dimension being subtotaled has a single member value in the current context, the granularities of the detail row computation and the subtotal computation are exactly the same and ATTR([foo]) will return foo’s value in both the detail row and subtotal row. There’s then no way inside a calculated field to have information to distinguish one computation from the other and return different results. Here’s a quick example:

1. Start a new workbook with Superstore Sales.

2. Put Department & Category on Rows.

3. Put ATTR(Category) on the Text Shelf.

4. Turn on Subtotals. ATTR(Category) returns * in all subtotals.

5. Filter out all Categories except Bookcases. ATTR(Category) now returns Bookcases in the subtotal for Furniture. Here’s a screenshot:

In the above view, for both the Bookcases detail row and the Furniture subtotal, ATTR(Category) returns 1, COUNTD([Category]) (or TOTAL(COUNTD([Category])) return 1, etc. For this particular situation there are no tests I’ve been able to find that would enable us to identify whether the calculation is for a detail row or subtotal row, if you can find one that would be great!

Siraj SamsudeenHi Jonathan,

>>I don’t see how testing for ATTR() returning asterisk or not would help in the single >>member dimension & subtotal case (and I’d love to be proven wrong).

Thank you for humility. You are right and it is me who is wrong here. I have written prematurely without thinking through it fully that having the ATTR test return True when it finds null will help me find a way to test reliably for a grand/sub total field when only 1 dimension member is available in the view. I stand corrected and I am grateful for that.

But asking myself ‘Is there any way to find out when we are in a subtotal/grand-total area’, I thought that I moved a little closer to the answer, but in the end, I realized that I did not succeed. Taking the example you have shown, I thought ‘If I can find the total number of categories before the filter on the category was applied and compare that to the total number of categories after the filter is applied, then this value will be different in the grand total cell’. So, I have a created a measure to return COUNTD(Category) and put on the Text shelf and created a filter using a Table-calc on Category – so, when I use that filter, the COUNTD at subtotal level for Furniture returns 4 whereas at the detail level it returns 1. If I test whether this value is greater than 1, I thought that I succeeded, but I did not. If the data source contains only 1 category, then we will end up with the same situation as we started with :). Anyway, this experimentation is at least helping me think through how Tableau is working.

Jonathan, Isn’t there a way to get the filtered total and unfiltered total at the same cell? In PowerPivot, this can be easily accomplished by clearing the filter in one calculation and keeping the filter in another calculation which was very intuitive and brilliant and solved most of these situations in a simple way. But in Tableau, I have not found any equivalent to clearing the filters when you are working on a particular calculation – do you have any thoughts/ideas?

Jonathan DrummeyPost authorHi Siraj,

I’m glad this is helping your learning process! You’d asked, “Isn’t there a way to get the filtered total and unfiltered total in the same cell?” There are ways, but none of them are as simple as what you describe in PowerPivot. The main way to get a total that isn’t filtered available to a cell is to use a data blend, but when data blends & grand totals get involved that can be tricky. An additional possible way to get an unfiltered total is to use a table calculation filter, since table calculation filters are only applied after most computations are complete. If all you need is a boolean result, sometimes a computed Set can be used (since the conditional or top Set is computed before most filters are applied). Finally, there’s always doing pre-aggregation outside of Tableau.

Siraj SamsudeenBy the way, how do you quote the message and insert screenshot in reply to a post?

Jonathan DrummeyPost authorSince I’m the site admin, I’m usually answering comments inside the WordPress dashboard where there’s more capability than the front-facing editor.

Siraj SamsudeenHi Jonathan, I am posting different comments about questions related to different areas so that it is easy to track your answers against different questions. Is this OK for you?

You mentioned about looking into the log files of Tableau to find out what kind of query it is sending. Can you please provide pointers to understand the queries written by Tableau in log file? In this case of superstore XL file, I could not find any SQL statement, rather queries reference the extract and uses some internal language which I could not really decode – any help on this please? I am trying to improve my knowledge of how Tableau is working behind the scenes. So, any pointers to this would be greatly appreciated.

Jonathan DrummeyPost authorConnections to Tableau data extracts (and the new Excel & text file data connector introduced in v8.2) use an internally-developed query language. There’s no public-facing documentation on it that I’ve ever seen. I can read a bit of the language based on experiences building a view and then looking at the logs to see what Tableau did for the live connection (SQL) vs. extract (Tableu query language). The level of nesting in the Tableau query language is such that the easier thing for me to learn & debug connections with has been to use a live connection to the source (and in 8.2 specifically choose to use the legacy JET connector for Excel & text sources) so I can see SQL.

Siraj SamsudeenThanks for the pointers Jonathan. I have been able to look into the SQL query by using the legacy connector in 8.2. Even the SQL queries used by tableau seem to be using a lot of custom options, thus making it very difficult to read. Do you have any specific techniques you use to decode the SQLs?

Jonathan DrummeyPost authorMy main technique is to start small and iterate. For example, start out with a single pill in a view, refresh the view, and look at the query. Then make one change to a view (like add a single dimension or measure pill, or a filter) and then refresh the view, and look at the new query. Understanding can build pretty quickly, especially if you already have a SQL background.

Siraj SamsudeenIF FIRST()==0 THEN

WINDOW_SUM(SUM([Sales]),0,IIF(FIRST()==0,LAST(),0))

END

Hi Jonathan, in the above statement, why did you use this part – IIF(FIRST()==0,LAST(),0)? To me it looks like WINDOW_SUM(SUM([Sales]) will produce the same results as the default window will go from first to last. Please correct me if I am wrong.

Jonathan DrummeyPost authorIn Tableau prior to v8, the table calculation code had some inefficiencies, see http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html for the gory details. The ,0,IIF(FIRST()==0,LAST(),0) was necessary to improve performance, Tableau v8 made that unnecessary.

What the calculation does is make the WINDOW_SUM() sum over the first to the last address in the partition only for the *first* address in the partition, then for everything from the 2nd to last address in the partition the computation is WINDOW_SUM(foo,0,0) which only computes foo for a single address.

Siraj SamsudeenJonathan, can you please provide more explanation of this statement?

“For the Grand Total row, Category is removed from the overall level of detail. So the calculation is performed for eachvalue of Category (copy), i.e. the AVG([Sales]) is calculated for each value of Category (copy) and then all the results are summed together. Only this process happens 3 times, once for each value of Category (copy)”

When we are in the grand total cell, category is not available but we do have the category(copy) in the level of detail. I assume a query like this is sent to the DB: Select category(copy), AVG(Sales) from Sales group category(copy). Now, once these results come back, Tableau goes over the category(copy) as it is on the addressing field and sums up the AVG(Sales) value. But why does it happen 3 times – this is the part that I do not understand.

If it is convenient for you, please feel free to explain it in SQL terms as that would help me to visualize what is really happening. I really need to understand this. If I fully understand this, I think I might be able to move forward with the problem that I am stuck in trying to get a similar problem solved about which I posted here – http://community.tableausoftware.com/thread/153455

Jonathan DrummeyPost authorThat was a mistake on my part, everything after “So the calculation is performed for each value of Category (copy)…” is wrong, I’m not sure how that slipped. You’re right about the effective query to the DB.

David SchulerHi Jonathan,

Any tips on doing something like this on top of a calculated field? I used the second way of doing it, first with a standard measure (non-calculated) and it worked fine. I then tried to do it with a calculated field and I could not get it to work.

I’ve been using this calculation:

IF FIRST()==0 THEN

WINDOW_SUM(SUM([Sales]),0,IIF(FIRST()==0,LAST(),0))

END

but I have to get rid of the sum before [Sales] since my calculated field is already aggregated. Any ideas on how to make this work?

Jonathan DrummeyPost authorHi David,

You haven’t specified what level your calculated field is working at. There are three levels of calculation in Tableau – record level (which is what the raw fields in our data are), aggregate, and table calc (which is an aggregation an aggregate). SUM, AVG, MIN, MAX, ATTR, etc. are all aggregations, from your description I’m guessing your calculated field is using one of those (or is a table calc) and that’s why you had to get rid of the SUM() in the IF FIRST()==0… calc.

As to why the calculation isn’t working in the calc, there are many, many possibilities. The most likely explanation (since you’re posting here) is that your aggregate calc is depending on certain dimensions being present that don’t exist in the Subtotal or Grand Total computation. To give you a more exact answer, I’d need to see a packaged workbook with sample data, a link to a Tableau Public workbook, or if neither of those are possible a screenshot showing all your pills and a description of all the formulae of calculated fields and their compute usings, along with a description of what the desired results would be.

Two other things: The ,0,IIF(FIRST()==0,LAST(),0) optimization is not needed if you’re using Tableau v8 or later. Also, if your calculated field is a regular aggregate (i.e. doesn’t use table calcs or fields from another data source) and you’re on v8.1 or later you can avoid the need for the custom calculation and use the two pass totals feature, see http://drawingwithnumbers.artisart.org/tableau-8-1-two-pass-totals/ for more details.

Pingback: Tableau Tips,Tricks,Best Practices - Calculation - Jenny (Xiao) Zhang

VenkatHI Jonathan, I know this post is quite late, nonetheless, I would like to point out that sub-total for the first pill in the rows shelf, which has ‘Category’ and ‘container’ dimensions in that order, can aslo be manipulated. Plz let me know if I got it right ( worked for me).

if MIN([Product Category])==MAX([Product Category]) and MIN([Product Container])==MAX([Product Container])

THEN AVG([Sales])

ELSEIF MIN([Product Category])==MAX([Product Category]) and MIN([Product Container])!=MAX([Product Container])

THEN SUM([Sales])

elseif MIN([Product Category])!=MAX([Product Category])

then AVG([Sales])

END

Jonathan DrummeyPost authorHi Venkat, I’m not quite seeing what the goal is for what you’re doing? The calc is returning the Avg sales in the detail rows, the sum of sales in the Subtotal, and the Avg Sales in the Grand Total rows. So yes, subtotals can be independently manipulated from grand totals, which is something that I hadn’t made explicit in the post. Was that what you were trying to describe?

jackThanks for some very helpful ideas on how to work around Tableau on this. I have quite a tricky version of the issue here, any top tips would be very welcome!

https://community.tableau.com/thread/201526

thanks, Jack

Jonathan DrummeyPost authorHi Jack,

I responded on the forums, let me know there if it works for you!

Jonathan

LeeI want to thank you so much for posting this solution. However, I have a problem with this and highlight tables. Could you look at this post to see if you have any advice?

https://community.tableau.com/message/477614#477614

Jonathan DrummeyPost authorHi Lee,

I’ve responded on the forums.

Saj JayVery very convoluted solution to a problem that should not exist in a tabular view of data. In other words, the grand total in Tableau is completely wrong and this just proves it.

Jonathan DrummeyPost authorHi Saj,

I’m sorry you believe that, I’m of the opinion that there are some very good arguments for Tableau to behave the way it does. In particular for measures like averages and count distinct where an aggregation of an aggregation (such as averaging an average) would return misleading or incorrect results. Also, you might have missed it, at the top I made a note that Tableau introduced 2-pass totals in 2013 which do support the aggregation-of-aggregation case in many (but not all) situations. And Level of Detail (LOD) expressions introduced in 2015 have resulted in even less need for the techniques I described in this post.

Jonathan