
1. Re: Calculating an average of an average with Grand Total
Tracy Rodgers Jun 4, 2012 2:14 PM (in response to Tania.Skinner)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
Tania.Skinner Jun 5, 2012 11:44 AM (in response to Tracy Rodgers)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

SampleData.twbx.zip 11.4 KB


3. Re: Calculating an average of an average with Grand Total
Jonathan Drummey Jul 26, 2012 9:18 AM (in response to Tania.Skinner)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