14 Replies Latest reply on Jul 8, 2013 10:18 AM by Jonathan Drummey

# I'm pretty confused about the whole subtotal/total thing...

When I have subtotals turned on, I get some really strange results.

I've read the forum posts about how to set up a TOTAL instead of an AVERAGE for grand totals, etc., but to be honest, that seems like a seriously convoluted messy hack.  Why is there no option to just set the column totals to what we want?

Anyway... someone please explain why I see these:

In Excel, the last column should be 862,811.  If it was an average it would be 66,730.  No idea where 5 million comes from. - this is the per-record level of data.

Same thing here...   different tableau workbook, different data... same wonky result.  Not an average or a total.

• ###### 1. Re: I'm pretty confused about the whole subtotal/total thing...

It would probably be a lot easier to troubleshoot if you have a workbook to play with.

Could you add some version of your data+workbook that allows for a little testing of the data?

Thanks

Dana

1 of 1 people found this helpful
• ###### 2. Re: I'm pretty confused about the whole subtotal/total thing...

Unfortunately, I can't.  It's confidential data and I'm not sure I could recreate this by just making up numbers. lol -- there are some pretty complicated calculations going on, which I'm sure is where the problem lies..

1 of 1 people found this helpful
• ###### 3. Re: I'm pretty confused about the whole subtotal/total thing...

Here is a link to part one of a series on grand totals. It may help:

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

1 of 1 people found this helpful
• ###### 4. Re: I'm pretty confused about the whole subtotal/total thing...

Thanks Alex.  I guess I'm going to have to bite the bullet and study that blog.  I read/skimmed it once and thought to myself "ouch, this is going to hurt" and I tried a part of it out but it seemed like I was creating a whole new grand total that didn't look or feel like the ones built in...

1 of 1 people found this helpful
• ###### 5. Re: I'm pretty confused about the whole subtotal/total thing...

Yeah, this simply doesn't work for me because I don't want to/can't put the worksheet on a dashboard due to it's size and formatting issues.

There has to be an easier way to accomplish actual totals.  - I also need SUBtotals that are real live subtotals and not averages...

1 of 1 people found this helpful
• ###### 6. Re: I'm pretty confused about the whole subtotal/total thing...

You're going to have to mock data up, or anonymize it.

1 of 1 people found this helpful
• ###### 7. Re: I'm pretty confused about the whole subtotal/total thing...

Seriously, what is this doing?!

Tableau reports one number which isn't the Median, Sum or Avg of the column... what is this number?   It's pretty useless if I can't get a simple column sum.

I know it uses the most granular calculation, average or whatever if it was used in the numbers associated but GAH this is very frustrating.

This final column is a calculation:

[Old Cost 1]: 1+zn(avg([Some Dollar Amount that is the same for all records]))

[New Cost 1]: 1+zn(avg([Some other Dollar Amount that is the same for all records]))

U: [Old Cost 1]+max([Old Cost 2])+[Old Cost 3]

V: [New Cost 1]+max([New Cost 2])+[New Cost 3]

X: [V]-[U]

Final: [X]*[CountDofUnits]

[Old Cost 2]: if left([UnitType],6)='1' then [Cost 1] else [Cost 2] end

Now, if I change Old Cost 2 to: attr(if left([UnitType],6)='1' then [Cost 1] else [Cost 2] end)

I can at least get the Grand Total to stop reporting whatever that number was that it was using and instead it leaves the grant total blank.

1 of 1 people found this helpful
• ###### 8. Re: I'm pretty confused about the whole subtotal/total thing...

Let's do this.

I have [miles per day].

I take [miles per day]*365

I put that on my sheet and ask for the AVG of it.

Now I take avg([miles per day])*365 and put that AGG on my sheet.

So now I see this:

The subtotals are yellow and the Grand Total is green.

The subtotals aren't sums, averages or medians.

The grand totals aren't sums, averages or medians of the individual units or the sub totals or even the individual units + the subtotals.

1 of 1 people found this helpful
• ###### 9. Re: I'm pretty confused about the whole subtotal/total thing...

What does the raw data look like going into this? Literally what you posted?

1 of 1 people found this helpful
• ###### 10. Re: I'm pretty confused about the whole subtotal/total thing...

Pretty much yes.  I think I'm going to send this on to Support though and let them look into it simply because I have about 10 calculations under each column all tied together  lol

1 of 1 people found this helpful
• ###### 11. Re: I'm pretty confused about the whole subtotal/total thing...

"Pretty much" yes, or yes? Don't take that the wrong way, but without the raw data this is really difficult to diagnose.

1 of 1 people found this helpful
• ###### 12. Re: I'm pretty confused about the whole subtotal/total thing...

Yes.

Sorry, I didn't mean to be "iffy" where data was concerned.

I hope support can help and they find that it's some strange join or link or something that I did wrong because I worked on this for four hours today and can't get past this dang grand total issue.  Really not good for the old confidence you know?

1 of 1 people found this helpful
• ###### 14. Re: I'm pretty confused about the whole subtotal/total thing...

I'm sorry you're frustrated, and I'd like to help you get the results that you need. I've found with grand total and subtotal computations that I can almost always return the desired results, the only times that haven't worked have been due to text table formatting requirements that were beyond Tableau or some seriously complicated table calculations.

Tableau computes the subtotals and grand totals with the same formula of the calc, as a separate computation at a higher/coarser level of aggregation. In the case of a grand total for a column, you can generate a duplicate result by creating a new worksheet and taking all the dimension pills off of Rows. So, for example, if you have a SUM(Sales) broken down by Department (on Rows), the Grand Total will be the sum of all sales, which is the same as if we summed the individual rows. In the case of AVG(Sales), then the Grand Total will show the average sales across the entire data source, in other words ignoring Department.

Here's one example of how this works in practice: I'm almost certain that the reason why your Old Cost 2 calc with ATTR() doesn't return a Grand Total is because in the Grand Total computation, there will be multiple values returned by the inner calculation and that will make the ATTR() return Null/*.

In the case of your other calcs, in the Grand Total they are returning MIN() and MAX() values across the data source (i.e. only grouped by dimensions on Columns), so the the inputs to the calcs are probably not what you're thinking they would be, and that's why the output doesn't seem to be computable. However, it is very much computable, it's just a different form of logic than you might be expecting.

One way I work through trying to customize complex grand totals is to:

1) Build the original worksheet.

2) Build a worksheet that duplicates what Tableau is doing in the Grand Total (or Subtotal).

3) Identify the variance between the two. For example, the original worksheet might require a given dimension in the overall level of detail, but if that is only on Rows then it will go away in a Column Grand Total.

4) Build the necessary calculation(s) to return an accurate result for the Grand Total.

5) Create a worksheet that returns the correct results.

I'd be happy to go over your workbook either privately in email or a webex, my email is jonathan (dot) drummey (at) gmail.

Jonathan