10 Replies Latest reply on Feb 17, 2015 12:55 PM by Matt Lutton

# Grand Total Manipulation

Hey Everyone,

I know that Tableau while doing a grand total (from Analysis --> Totals) excludes the row level grouping and operates on data as a whole.

I am doing an average cost per ID and when doing the Grand total,  its giving the wrong total (Please see attached workbook).

Please help me get the correct Grand Total. I want 369 as the total instead of 61.27 as derived in the workbook.

Thanks

Tiru

• ###### 1. Re: Grand Total Manipulation

Right click the AGG(Average Per ID) pill and choose "Total Using">>"Sum".  You may need to re-format to two decimal places after doing so. 1 of 1 people found this helpful
• ###### 2. Re: Grand Total Manipulation

Thanks for the Reply Matt, Appreciate it.

My end goal is to show a running sum and Grand total Equivalent to last record.

So in the workbook Attached the last ID = 6 will have the running sum as 369,

Also the Grand total = 369 (The "Total Using" Grey's out when doing a running total).

I wanna do that in one sheet only, not using two sheets (one down below) in the dashboard.

Let me know if you need more clarification.

Thanks

Tiru

• ###### 3. Re: Grand Total Manipulation

I understand what you're asking for - can I ask why you would need the grand total if performing a Running Total?  I ask because with a Running Total, the last record would always be equivalent to the Grand Total.

I'm sure there's a way, but it gets trickier to customize grand totals when working with table calculations.

• ###### 4. Re: Grand Total Manipulation

Hey Matt,

Its how the requirement is Matt. Grand total to be below the running total (Last Record)

Help would be appreciated.

-Tiru

• ###### 5. Re: Grand Total Manipulation

I am not sure how to do this in one sheet, given the Running Total calc.  I'd either go with two sheets, or simply explain to whoever is requiring this that it does not make much sense since the value is already provided.

• ###### 6. Re: Grand Total Manipulation

Thanks Matt

• ###### 7. Re: Grand Total Manipulation

And I'm sure there's still a way to accomplish what you want in one sheet -- but I've tried several variations with no luck.  Perhaps Jonathan Drummey has time to teach us a thing or two here.

• ###### 8. Re: Grand Total Manipulation

See the attached. I used the duplicated dimension trick from http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/ and two nested table calcs. One is a running sum that has a Compute using on both ID and ID (copy) and that gets the values, then the second one is IF LAST()==0 THEN [RS Average Per ID] END and that has a *nested* compute using on just the ID (copy) so it returns one value per cell: Then to make the final view I duplicated this worksheet, got rid of the unecessary in-process measures, Ctrl+dragged a copy of the Display calc to the Filters Shelf to filter for non-null values, and turned Analysis->Stack Marks Off to get rid of mark stacking in the Grand Total that makes the view wider.

To get a custom grand total of a table calc we have to do several things:

1) Make sure that the grand total computation has the necessary level of detail/granularity to get accurate results for the original aggregate measures in the grand total computation.

2) Given the changes for #1, adjust the original measures as necessary so they return accurate results in the detail rows.

3) Adjust the original table calculation as necessary so it works in both the detail rows and grand total. If there are nested table calcs, this step will be repeated multiple times.

4) Build a nested calculation that will pass through the detail row totals as well generate the custom grand total.

If one or more data blends are involved that adds a whole new dimension of complexity because for each blend there are effectively 2+N blends, 1 for the detail rows, 1 for the grand total, and N more for all the different levels of subtotals.

Jonathan

• ###### 9. Re: Grand Total Manipulation

Wao, Thanks Jonathan.

You are a rockstar.

-Tiru

• ###### 10. Re: Grand Total Manipulation

Excellent, thank you Jonathan!