8 Replies Latest reply on Jul 23, 2015 4:29 PM by Rajesh Bafna

# Grand Total - Sum of Averages within a calculated field

I want the Grand Total aggregation to do Sum of Average within a calculated field

Look at the excel snap shot to see how I need it to calculate:

As you can see the Grand Total TARP calculation should be:

Sum(Impression for a market)/ Sum (Average ( Universe for a Market))

I have also attached the Tableau Report. I am unable to get Tableau to calculate the Grand Total TARP Calculation the way excel is doing it. I would like it to be 29.86. However in Tableau my calculated field is generating it as : 59.72

I did go through a couple of forum topic but no luck:

Grand Total Average Using Multiple Measure Names/Values

Customizing Grand Totals – Part 2 | Drawing with Numbers

Any assistance on this will be highly appreciated.

Cheers,

Aksha

• ###### 1. Re: Grand Total - Sum of Averages within a calculated field

Hi Aksha

I see that the Sub total values for ( Average of universe) in your workbook are not matching with the values in  the excel sheet. I think that should be the root cause to your issue. Try rectifying those values and give a try. Hope its solves your issue.

Thanks

Karthik

• ###### 2. Re: Re: Grand Total - Sum of Averages within a calculated field

Thanks vishwanath Pendyala

Fixed the sub total, but the grand total is not changing . Please have look at the attached Tablea Report as well.

• ###### 3. Re: Grand Total - Sum of Averages within a calculated field

Hi Aksha,

My understanding is that since your sub-totals are average, the grand total will also be calculated as an average. In the column for 'Average of Universe' the grand total is the average of 692,737 and 61,178.

In Excel you seem to calculate the sub-totals as average and the grand total as sum. I don't think this is possible in Tableau - please someone correct me if I am wrong?

Cheers,

Julia

1 of 1 people found this helpful
• ###### 4. Re: Grand Total - Sum of Averages within a calculated field

Since Jonathan Drummey seems to be able to put anything he wants in the Grand Total column, Julia I suspect you are probably, maybe, slightly, sort of wrong. But I have no way of really knowing because I don't possess Jonathan's Grand Total chops. This lack of knowledge on my part is somewhat a lack of remembering how to do all the nifty tricks Jonathan has taught me, and part a protest against the incredibly dense "stuff" we need to learn/remember just to use the product ... a supposedly drag 'n drop product. [I commented just today to a friend that it is ironic that I originally gravitated to Tableau because it was a drag 'n drop data viz engine, but I find myself several years later having just had to learn SQL to provide my client the solution they were looking for.]

Of course this is all about text tables, so I can't really complain about how difficult they are to create, because I really would prefer they went away altogether.

Cheers,

--Shawn

• ###### 5. Re: Grand Total - Sum of Averages within a calculated field

Thanks for the vote of confidence, Shawn! Yes, it is possible to get the desired result, and yes, this is complicated. I’ve come to think of customizations of subtotals & grand totals with the metaphor of solving a Rubik’s cube, in the dark, while wearing thick rubber gloves. We have to know the granularity of our data, the granularity of the view (based on the dimensions in the view), and the different granularities that each subtotal and grand total are working with, only we can’t directly see those for the subtotals and grand totals so we have to imagine them. Then we have to set up a measure that does the right aggregation at each level, with the proper addressing and partitioning of table calculations (since we’re almost certainly going to have to use a table calc).  Since we don’t get visibility into each level of computation, we have to be able to imagine the entire computation all at once to get the desired results.

I used a variation on the subtotals instructions from http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/ here. There’s a duplicate of the Program dimension on the Level of Detail, so we can have the Programs available in the Grand Total computation to be able to sum the averages of the Universe values. As I’d noted in the Customizing Grand Totals blog post, all the measures have to be revised to handle that extra level of detail:

Market (revised)

IF FIRST()==0 THEN WINDOW_SUM(SUM()) END

Universe (revised)

IF FIRST()==0 THEN WINDOW_SUM(AVG()) END

TARPS (revised)

/[Universe (revised)]*100

All of these have a Compute Using on the Market (copy) dimension, and here’s the result:

To create the view, there were two other steps, covered in http://drawingwithnumbers.artisart.org/customizing-grand-totals-in-tableau-v8-the-stacking-snag/. I Ctrl+Dragged a copy of the TARPS measure from Measure Values onto the Filters Shelf, setting it to filter for non-Null values, and then turned Analysis->Stack Marks off.

Workbook is attached.

Jonathan

• ###### 6. Re: Grand Total - Sum of Averages within a calculated field

Jonathan Drummey

Hi Jonathan

Absolutely Brilliant!!! . Thank you very much for this. Highly appreciate it.

Oh wow.. am so happy ..

Cheers,

Aksha

• ###### 7. Re: Grand Total - Sum of Averages within a calculated field

You’re welcome!

• ###### 8. Re: Grand Total - Sum of Averages within a calculated field

Hi Jonathan - That was very insightful. I have an issue that your suggestion half solves it. The grand total on  column still doesnt reflect the sum of averages.

I have attached a workbook. It would help a great deal if you could help me with what I am missing.