putting grand total together

Customizing Grand Totals – Part 2

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.

Continued in Part 3 of this series.

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.

To follow this material, you should have an understanding of Tableau’s table calculations and partitioning. Having gone through the Top 10 Table Calculations and read the Table Calculations in Tableau 6 white paper should be a sufficient start.

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:

desired grand total

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):

putting grand total together

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:

  1. Identify how and when Tableau is computing the grand total row
  2. 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.

 

50 thoughts on “Customizing Grand Totals – Part 2

  1. Joe Mako

    This 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.

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

  3. Cathy Bridges

    Wow – 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!!

    Reply
      1. Cathy Bridges

        Thanks! 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!!

        Reply
  4. Chad K

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

    Reply
    1. Joe Mako

      Chad, 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.

      Reply
    2. Jonathan Drummey Post author

      Hi 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.

      Reply
  5. yogesh kumar

    hi 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.

    Reply
    1. Jonathan Drummey Post author

      Please 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.

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

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

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

    1. Jonathan Drummey Post author

      I’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.

      Reply
      1. Siraj Samsudeen

        Hi 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.

        Reply
  9. Siraj Samsudeen

    Hi 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.

    Reply
    1. Jonathan Drummey Post author

      Hi 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:

      ATTR(Category) returns a single value in both the detail row and subtotal

      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!

      Reply
      1. Siraj Samsudeen

        Hi 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?

        Reply
        1. Jonathan Drummey Post author

          Hi 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.

          Reply
  10. Siraj Samsudeen

    Hi 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.

    Reply
    1. Jonathan Drummey Post author

      Connections 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.

      Reply
      1. Siraj Samsudeen

        Thanks 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?

        Reply
        1. Jonathan Drummey Post author

          My 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.

          Reply
  11. Siraj Samsudeen

    IF 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.

    Reply
    1. Jonathan Drummey Post author

      In 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.

      Reply
  12. Siraj Samsudeen

    Jonathan, 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

    Reply
    1. Jonathan Drummey Post author

      That 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.

      Reply
  13. David Schuler

    Hi 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?

    Reply
    1. Jonathan Drummey Post author

      Hi 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.

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

  15. Venkat

    HI 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

    Reply
    1. Jonathan Drummey Post author

      Hi 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?

      Reply
  16. Saj Jay

    Very 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.

    Reply
    1. Jonathan Drummey Post author

      Hi 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

      Reply
  17. Kerri

    I have a bit of a dilemma with this. I have a workbook that is summing a player metric from multiple database sources. I have only one dimension in the Row for “Game Name”. The Game Name is a static Excel sheet with the list of Game Names and Game Types that my sources can connect to (I am using this as the primary source in my sheet since coding all of the games and types with my complicated SQL would have been more work. Ideally I would be unioning these in Tableau, but they do not support this from multiple database sources yet). When I use the following it works as intended:

    IF MIN([Game Name]) != MAX([Game Name])
    //Grand Total Result
    THEN SUM([A Metrics].[A Players]) +
    SUM([B Metrics].[B Players]) +
    SUM([C Metrics].[C Players]) +
    SUM([D Metrics].[D Players])
    ELSE
    //Detail Row Result
    IF ATTR([Game Name]) = ‘A’ THEN SUM([A Metrics].[A Players])
    ELSEIF ATTR([Game Name]) = ‘B’ THEN SUM([B Metrics].[B Players])
    ELSEIF ATTR([Game Name]) = ‘C’ THEN SUM([C Metrics].[C Players])
    ELSEIF ATTR([Game Name]) = ‘D’ THEN SUM([D Metrics].[D Players])
    ELSE 0 END
    END

    However, when I add a filter to filter on a specific Game Type, the Grand Total no longer works. Is there a way to either blend the data better or a different function that could work around this? I’m really trying to avoid some of this in SQL since I will be adding more and more games.

    Reply
    1. Jonathan Drummey Post author

      Hi Kerri,

      I’m really glad to read that you’ve considered a union first as that would avoid these problems. Unfortunately I can’t answer your question without knowing the relationship between Game Type and Game Name fields in the situations before *and* after you filter for a specific Game Type. Also, when you say the “…the Grand Total no longer works.” what is the behavior that you’re seeing? My suspicion is that when you are filtering for a specific Game Type the data is ending up in such a way that the calculation is failing to detect the difference between the Grand Total and detail rows.

      Since you mentioned unions, complicated SQL, and adding new games I’m suggest that you check out one of the visual data prep tools like Alteryx http://alteryx.com or Trifacta http://trifacta.com. I also lived in the “munge different databases together with homegrown queries, extra tables in Access or Excel, custom queries, etc.” space and started using Alteryx last year. I was able to take a data prep process for Tableau that took 3-5 days per month of effort (the documentation of steps for this process it was 16 pages long) and turned it into a single click in Alteryx, my time savings on that one process alone was worth more than the cost of the Alteryx license. [Full disclosure: I’m now at DataBlick http://datablick.com and we’re an Alteryx partner.] Being able to prepare a fully unioned/cleansed/joined data source so we don’t have to write complicated & sometimes fragile calcs in Tableau is fantastic, though for me the even bigger win with Alteryx has been that its workflows are much easier to document than SQL logic, so that 16 pages of documentation plus a ton of SQL comments turned into a series of tools, visual layout, containers, and annotations in Alteryx and I was able to know that the person after me (including me coming back to the workflow weeks or months later) could more easily understand what the intent of each step was and make any necessary changes.

      Reply
      1. Kerri

        Hi Jonathan,

        The relationship is a specific Game Name will only have one Game Type associated with it: either Mobile or Console. I have a feeling that your statement “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.” is something similar to what is happening in my case.

        I had a thought that I may just duplicate my Game Name Excel sheet and use one as the primary source for the Mobile games and one for the Console games. The client has a specific way they want to see the dashboard which makes writing this a little limited and I’m trying to get crafty in how I write it so it’s not too complicated on both the SQL side or the Tableau side.

        Overall, your write up is very informative and I have learned some new tricks regardless if it is not the exact outcome I had hoped! Thanks again.

        Reply
        1. Jonathan Drummey Post author

          Hi Kerri,

          What you wrote in the first paragraph sounds about right, sometimes we can write the calculations to be smarter about dealing with the single dimension member situation but that can end up getting more effort than it’s worth, especially given what you write about other complexities.

          Jonathan

          Reply
  18. LENZy

    I have a grand total problem.

    I have two data sources that are blended. I created a calculation the multiples max car price from data source 1 times sum of cars sold from data source 2. For example, the Ford Fusion at a maximum price of $30,000 per car times 1,000 Ford Fusions sold, gives me $30,000,000.

    The calculation works fine at the car level (Ford Fusion) But the calculation does not work at the subtotal level (All Ford Cars) and grand total level (All Cars in America). For example, the Subtotal for Ford take the Maximum price for All Ford Cars (for example $70,000) time the total number of All Ford Cars sold (3,000,000), for a subtotal of $21,000,000,000.

    It takes too long to perform a join in Tableau (hours), instead of a blend. Otherwise, I could do a join, and a Level of Detail Calculation.

    And I was not able to figure out how to apply the grand total strategies above to blended data.

    Reply
    1. Jonathan Drummey Post author

      Hi LENZy,

      Data blending and grand totals can be challenging because we’re simultaneously working at six levels of detail: the grain of each data set, the grain of the blend (based on your linking dimensions), the viz Level of Detail aka vizLOD (based on the dimensions in the viz), the subtotal LOD, and the Grand total LOD. Without more details I can’t guide you through what needs to be done. If you can post some sample data (such as a viz with sample data on Tableau Public) and link back to it here I’ll take a look, though it might not be until after the Tableau Conference next week.

      Jonathan

      Reply

Please add your thoughts and perspectives