3 Replies Latest reply on Jul 28, 2012 5:55 AM by Eric Frishman

# Total Calculations:  Need to Change

Tableau Experts - This one should not be much exercise at all.  I don't know why I haven't found the solution yet because is just seems so simple I'm thinking it should be obvious.

Ok.  I have computed the specific values which I want.  I have traffic which I have appropriately averaged.  The issue is that when I ask Tableu to show Totals, the total I want is the sum of the averages (as shown in the worksheet), and not the average of the averages which is what is provided.

Am I missing something simple (probably - it has been that kind of week)?

Thank you for the help.

Thanks!

Eric

• ###### 1. Re: Total Calculations:  Need to Change

Hi Eric,

The good news is, you're not missing anything obvious! Tableau grand totals and subtotals use whatever the aggregation that is on the measure. However, there are some ways around this.

By creating a calculated field similar to the following:

IF FIRST()==0 THEN

WINDOW_SUM(avg([Sales]),0,IIF(FIRST()==0,LAST(),0))

END

Then, create a duplicate of your market field (right click on it in the data box and select Duplicate). Place the duplicate on the level of detail shelf. Place the above calculation on the view, right click and select Compute Using-->market (copy)

Hope this helps!

-Tracy

• ###### 2. Re: Total Calculations:  Need to Change

Tracy,

Thank you for such a quick reply and I know you've seen something similar to this probably many times.  I'm following through your example (I understand the formula).  I have duplicated the market (not originally named 'Market (copy)' ) but I am uncertain where the calculation gets loaded (it is named avg95_sum - not that that matters).

I have enclosed my screen on Sheet #2 of where things are.  We have to be close (actually it is all you)!!!!  I'm not certain about the compute using reference (yes I know how to reference it and need to figure out what that is doing and will research to better understand).

I have loaded the copy on the detail shelf.  If I put the calculation on the column I get graphics, and as a discrete it isn't quite right.  Is it possible to keep the text of the table, and have the calculation come up as a column at the end (much like a grand total does)?  I ask because I reference this column to determine appropriate coloring of each cell whether it is > than a % of the grand total (which is the sum of average - the last column).

Sorry you are having to drag me through the answer and thank you very much!!!  The results from Tableau are getting a great response.

Thanks!

Eric

• ###### 3. Re: Total Calculations:  Need to Change

Tracy,

With a clearer head I worked through your example and it is functional (of course).  I moved the formula into the text mark and it worked.  I need to think about the recompute with the copy, but thank you !