3 Replies Latest reply on Jul 26, 2012 9:18 AM by Jonathan Drummey

# Calculating an average of an average with Grand Total

I have a set of data that looks like:

 Product Criteria Met Apple Criteria 1 1 Apple Criteria 2 1 Apple Criteria 3 0 Apple Criteria 4 1 Apple Criteria 5 0 Banana Criteria 1 0 Banana Criteria 2 1 Banana Criteria 3 0 Banana Criteria 4 0 Cherry Criteria 1 1 Cherry Criteria 2 1 Cherry Criteria 3 1 Cherry Criteria 4 1 Cherry Criteria 5 1

I want to summarize the data above to show the average % of criteria for each product that is Met (or = 1).   I can create the following summary with a Grand Total average % as follows :

 Avg Apple 60.0% Banana 25.0% Cherry 100.0% Grand Total 64.3%

The problem is that the Grand Total is not giving me the average of the averages listed.  It is recalculating the average based on the lowest level of data across the products as the total number of  criteria met/total number of criteria so it is calculating 9/14 = 64.3%.  I want it to calculate the  average of each product's average so average(60, 25, 100) = 61.7%.   How can I do this with a Grand Total or a table calculation?

Thanks for the help!

• ###### 1. Re: Calculating an average of an average with Grand Total

Hi Tania,

The best way to do this is to find the desired total and place it on a separate worksheet, then place the two sheets on a dashboard together. One way to get the 61.7% is to create the following calculation:

(if last()=0 then [Calculation1] end)/total(countd([Product]))

Then, use the last row (not the grand total) and hide the other rows and headers. Hope this helps a bit!

-Tracy

• ###### 2. Re: Calculating an average of an average with Grand Total

Hi Tracy,

Thanks for the tip.  I don't quite understand.   Attached is a packaged workbook with the sample data.  Can you show me in the attached workbook?

Thanks!

Tania

• ###### 3. Re: Calculating an average of an average with Grand Total

Hi Tania,

Attached is a sample workbook with two options:

Option 1 has a dashboard like Tracy described, where there's the original worksheet and a second worksheet that just shows the total. I used a different calculation (WINDOW_AVG) that doesn't require a COUNTD (which requires an extract if you are using an Excel, Access, or text data source).

Option 2 uses a technique developed by Joe Mako that I documented here http://community.tableau.com/thread/116854 to use a custom calculation for the Grand Total row. It makes use of two table calculations, one to flag the grand total row and the other to return the appropriate value.

Hope this helps!

Jonathan