Grand Total Dashboard

Customizing Grand Totals – Part 1

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.

Also see Part 2 and Part 3 of this series.Note: After initial post there was a small edit for clarity, per Joe Mako’s correction.

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:

  1. Create a worksheet with the data.
  2. Duplicate the worksheet to create the grand total worksheet.
  3. 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:
    Grand total worksheet for dashboard
  4. 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.

  1. Create another calculated field – I called it Fake GT Header – that has “Grand Total” as the formula. Drag that onto the Rows Shelf.
  2. Now to set up the formatting. Right-click on the Grand Total and format the header to be Bold.
  3. Click on the Borders icon to set the Row Divider->Pane to None.Format Borders
  4. Right-click on the blue YEAR(Date) pill on the Columns shelf and uncheck Show Header.
  5. Now the dashboard is ready to be set up. Create a new dashboard using the Dashboard->New Dashboard menu.
  6. Drag a Vertical Layout Container onto the dashboard from the Layout Containers section on the left.
  7. Drag the original data worksheet onto the dashboard from the Dashboard window on the left.
  8. Drag the grand total worksheet onto the dashboard from the Dashboard window on the left and place it just below the original data worksheet.
  9. 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:Grand Total Dashboard

 

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:

 

 

 

39 thoughts on “Customizing Grand Totals – Part 1

  1. Andy Kriebel

    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.

    Reply
      1. Jonathan Drummey Post author

        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

        Reply
  2. Joe Mako

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

    Reply
    1. Jonathan Drummey Post author

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

      Reply
      1. Joe Mako

        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!

        Reply
  3. Pingback: Tableau 8.1 Two Pass Totals | Drawing with Numbers

  4. Alejandro

    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!

    Reply
  5. Jonathan Drummey Post author

    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?

    Reply
  6. Dave Whitney

    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]).

    Reply
    1. Jonathan Drummey Post author

      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.

      Reply
  7. Siraj Samsudeen

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

    Reply
  8. Jonathan Drummey Post author

    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.

    Reply
    1. Siraj Samsudeen

      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.

      Reply
    2. john

      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

      Reply
  9. Victor

    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?

    Reply
    1. Jonathan Drummey Post author

      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.

      Reply
  10. Bryan E

    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

    Reply
    1. Jonathan Drummey Post author

      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

      Reply
  11. Kate F

    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?

    Reply
    1. Jonathan Drummey Post author

      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.

      Reply
  12. prabhuraj.ravi@gmail.com

    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 .

    Reply
  13. Pingback: LOD Expressions and Separate Custom Grand Totals for Rows and Columns | Drawing with Numbers

  14. arya

    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

    Reply
    1. Jonathan Drummey Post author

      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?

      Reply
      1. m

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

        Reply
        1. Jonathan Drummey Post author

          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.

          • The finest grain is the granularity of the raw data, i.e. what makes a record. For example in the data source you posted the grain is the Sno and Date.
          • Then there is the viz level of detail (vizLOD) that is made of the dimension(s) in the view (i.e. on Rows, Columns, Pages, and the Marks Card). In the above text table you posted the vizLOD is Sno and Year of Date. When we bring measures into the view they are aggregated to the vizLOD. Therefore AVG(Sales) in this data is aggregated to Sno and Year of Date, and since there is only one record per Sno and Year of Date the AVG(Sales) is the same as the record-level Sales, which is why you see the original values in your data, such as 300 for CCC/2010, 100 for XXX/2010, etc.
          • The next level of detail is the grand total level of detail or GTLOD which is used for Tableau’s default Automatic aggregation in grand totals. The gtLOD effectively removes dimensions from the vizLOD based on the kind of GT it is. A GT on Rows removes all dimensions that are on columns and a GT on Columns removes all dimensions on Rows. So in the data you posted the GT on Columns is removing the Sno from the grand total computation. So the AVG(Sales) is being computed at the level of detail of Year of Date across all Sno’s. This is what I was referring to as the “performs the average at a higher level”, the “higher level” is referring to the fact that dimensions that are part of the vizLOD are removed from the GTLOD calculation.

          Now let’s talk about Superstore in this same context:

          • Superstore’s grain of data is the order item.
          • The view you posted above has a vizLOD using Product Category and Year of Order Date. So the AVG(Sales) is being computed across all the rows for each Product Category & Year of Order Date.
          • In the Grand Total on Columns the GTLOD is effectively removing Product Category (the dimension on Rows) from the level of detail and therefore the GTLOD is at the level of Year of Order Date. So the Grand Total for AVG(Sales) is being computed at the level of Year of Order Date and the AVG(Sales) for 2010 is 3160.

          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

          Reply
      1. m

        i mean the example you have mentioned , which superstore data you used , because there are different version of superstore data

        Reply
  15. arya

    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 ?

    Reply
    1. m

      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,” ?

      Reply
  16. Kiran Ravindran

    Hi All,

    Can anyone please tell me how do we perform “Fixed” LOD expression in 8.3?

    Thanks,
    Kiran

    Reply
    1. Jonathan Drummey Post author

      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

      Reply

Please add your thoughts and perspectives