7 Replies Latest reply on Aug 1, 2016 3:45 AM by Carl Slifer

# SubTotals and Totals Question

Hello,

Is it possible to disable row totals for all rows except the subtotal and grand total rows??

I have the following worksheet:

In the Total Column, the row totals of (38, 37, 37, 17, etc.) have no real meaning and I would like to suppress them. I do however want to see 129 (and the 211 etc) which is a row total on the subtotal.  Is there a way to do this?

Thanks!

Antoinette

• ###### 1. Re: SubTotals and Totals Question

You might be able to do this with some format trickery and format that part of the totals to white where it does not show.  I will try this on a mock workbook.

• ###### 2. Re: SubTotals and Totals Question

I could not find any format option that targeted just that section of the totals

• ###### 3. Re: SubTotals and Totals Question

Howdy Antoinette,

I've a solution for you. In order to understand how it works it might require some understanding of table calculations to a slightly greater level than point and click,

We have built two calculations that are table calculations. They simply check if FIRST() = LAST()

One of these basis it on running across the table and the other is based on partitioning by 1 field and addressing the other two.

In table calculations scope and direction matter. In this regard although the calculations look the same they are looking at a different set of fields. For instance a running total might run across the table or it might run down the table.  So in this regard we can check multiple conditions.

In our case we first find out if something is the subttotal or grandtotal. If it is then we return the sum(sales) if it is not then we check the next condition. The next condition is whether or not it is the first() AND the last() based on each row. Because totals start the table calculations over the totals areboth first and last. If they are first and last we exclude them and we show anything that is not first and last (based on the row) as the sum of sales.

There is most likely a more succinct solution for this but table calcs are my friend and they can be your friend too.

Cheers!

Carl Slifer

InterWorks

2 of 2 people found this helpful
• ###### 4. Re: SubTotals and Totals Question

Hello Carl,

Thank you SOOOO MUCH!  The example you posted is exactly what I wanted and I was able to apply it to my workbook successfully!!

Unfortunately I don't completely understand what I (or rather you haha) did and why it works!

Are there any discussion threads or reference articles or training videos that explain it in more detail?

Thank you!

Antoinette

• ###### 5. Re: SubTotals and Totals Question

Hi Antoinette,

Here are some informative blog posts that you might find helpful. I'd venture that only a very small fraction of Tableau users abuse table calculations in this manner in order to achieve the certain results. However because you're curious (that's a good thing) anytime you want a deeper dive you can find it.

https://www.interworks.com/blog/tmccullough/2014/09/29/5-tableau-table-calculation-functions-you-need-know

I would honestly recommend just playing with them as well. Use FIRST() and put it on your text shelf. Then use LAST() and do the same. Change your scope and direction (the compute using thing). Repeat with INDEX(), etc. Its one of those things that is hard to learn unless you have a challenge that duplicates the need or else its just like vocabulary words.  However knowing that FIRST() and LAST() and how they work inside a table and in totals can be fairly valuable.

Best Regards,

Carl Slifer

InterWorks

1 of 1 people found this helpful
• ###### 6. Re: SubTotals and Totals Question

Nice job Carl!

• ###### 7. Re: SubTotals and Totals Question

Thank you John. If only you knew why I know this. (very very very specific

requirements from certain clients). Happy always to impart knowledge and

lessen others pain when able.

On 1 August 2016 at 11:25, John Sobczak <tableaucommunity@tableau.com>