5 Replies Latest reply on Feb 12, 2013 10:55 PM by Jim Wahl

Multiple Total Rows

I am relatively new to Tableau and I have a challenging report that my team is building and we seem to be pushing the limits of Tableau at each turn.  My latest challenge is trying to figure out how to add multiple sub-total rows (beyond the menu option for sub-totals).  Below is a rough idea of what I want to display in a grid.  (Our visuals will come later.  )

In this example there are two categories (hierarchy)  Each communication category will have multiple communications with in it.  I want to not only use the built in sub-totals, but also display the % to Total under the Sub total.  This is not an optional requirement, so I need to figure out if this is possible.

The numbers below are the total number of mailings for each Category

My questions are:  Is this possible?

If so how?

Customer Communication Category: Mailings:

communication 1    100

Communication 2    200

Total:    300

% to Total: 30%

Customer Communication Category: Emails::

Communication 5  400

Communication 6  300

Total:  700

% to Total:  70%

Grand Total: 1000

Thank you in advance for any assistance you can provide!   If you need additional information please let me know.

• 1. Re: Multiple Total Rows

It's easy to add a second column with the %; for example

To get Option 1, just use the % of total table calculation (right-click on the measure > add table calc. > percent of total). After to do this, you'll need to re-add the SUM(Sales) measure to the shelf.

Option 2 edits the table calculation to compute using product type and then nulls out the 100% values with a small modification to the table calculation:

IF SUM([Sales]) / TOTAL(SUM([Sales])) == 1 THEN NULL

ELSE SUM([Sales]) / TOTAL(SUM([Sales]))

END

See attached workbook.

Jim

1 of 1 people found this helpful
• 2. Re: Multiple Total Rows

Jim, first, thank you for the quick response and an approprite example.

Option 2 is very close to what we need.  Unfortuantely the business requirment I have is to make Percent a second row not a second column.  I should have been more specific about that.

• 3. Re: Multiple Total Rows

Hi Shawn,

I expect this can be done, perhaps with custom SQL, such as Jonathan Drummey explains here:

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

I might also push back on the requirements, since having two lines for sub-total seems less clear to me. And, if you get it to work it'll be difficult to maintain, for someone else to understand, etc. ...

Jim

• 4. Re: Multiple Total Rows

Building from Jim's work, using his IF statement, combined with with some of the Grand Total techniques he linked to (the "Customizing Grand Totals with Table Calculations" section on http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/) you can get the attached.

Let me know if you have any questions.

• 5. Re: Multiple Total Rows

Cool! I knew it could be done, and it's easier than I thought.

One thing that had me scratching my head for a few minutes was how Joe changed the text for Total / Grand Total. Just choose Format from the dimension pill and edit the Header > Totals > Labels. ...