12 Replies Latest reply on Oct 26, 2015 7:24 AM by khaja mohiuddin

# How do I display the grand total for averages when using a table calculation?

Hi,

I am having trouble displaying the grand total for my average calculation. I've been following Jonathan Drummey's post on customizing grand totals

http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/ but I think that I am running into trouble because of my table calculations.

window avg calc:

IF FIRST()==0 THEN WINDOW_AVG(AVG([CAMP_TOT_COMMIT_AMT]),0,IIF(FIRST()==0,LAST(),0))

END

In the calculation above, I had to take the average of the CAMP_TOT_COMMIT_AMT because my totals were duplicating based on the number of  field visits (please refer to the workbook). The averages displayed per row come out correct but both the subtotals and grand total are incorrect. I did see a way to customize grand totals using MIN () and MAX () but I'm not sure how or if I can do that if I'm working with calculated fields. Any help would be greatly appreciated, thank you in advance!

• ###### 2. Re: How do I display the grand total for averages when using a table calculation?

Hi,

I took a look at this and the workbook is using WINDOW_SUM() while the calc you wrote here is using WINDOW_AVG().  As far as I can tell, the WINDOW_SUM() is working as it has been set up, namely totaling over each Country/Region. When you say that the totals are incorrect, what are the values you are seeing and what are the desired values?

Jonathan

• ###### 3. Re: How do I display the grand total for averages when using a table calculation?

Hi Jonathan,

Thank you for your reply, sorry if I am being unclear. Ultimately what I want is for both the subtotals and totals in the Average calc column (pg. 1 of the workbook) to display the sum of the averages and not whatever average it is taking right now. So I'd want the grand total for the average column to display \$7,332,088 and I want Tableau to display the respective subtotals for each region by summing up the averages for the countries within that region.

About the incorrect totals, I thought maybe I wouldn't be able to customize the subtotal and grand total because I was using a window_avg calc which was also taking the average of the measure. The incorrect totals are on page 2 of the workbook, it is the field labeled [CAMP_TOT_COMMIT_AMT] which shows the commitment amount multiplied by the number of field visits. Using the window calculations with the average function seemed to correct this problem but I wasn't sure if it would cause problems later when trying to customize grand totals.

• ###### 4. Re: How do I display the grand total for averages when using a table calculation?

See the attached, is this what you want? As far as I can tell, there's no need for Entity ID to be in the view, and since you want your subtotals to work at Country level then we duplicate Country and not the Entity ID.

Jonathan

• ###### 5. Re: How do I display the grand total for averages when using a table calculation?

Hi Jonathan,

I think this is getting close to what I want, the problem is that the field CAMP_TOT_COMMIT_AMT for the countries is wrong. If you pull CAMP_TOT_COMMIT_AMT into the table and view the underlying data you will see the rows duplicating based on field visit. For example, in the attached image, Entity ID 0020051321 has had 3 field visits so his/her campaign total commitment is counted three times but we only want it to count once. This is the case with any entity with more than one field visit. This brings Japan's commitment total to \$3,196,100 when it should be \$1,098,450.

• ###### 6. Re: How do I display the grand total for averages when using a table calculation?

Hi Julie,

Ok, I think I'm starting to get a handle on this...  However, I still don't see how your Grand Total average would be 7,332,088 when just the sum of the Hong Kong averages is 39,436,525. Check out Sheet 4 and Sheet 5 in the attached.

Jonathan

• ###### 7. Re: How do I display the grand total for averages when using a table calculation?

You're right Jonathan, the grand total average would not be 7,332,088, I think I got that number from adding up the totals from the window_avg calculation. Your campaign total numbers are correct and match what we have in our database for those countries. How would I find the average dollars by field visit? When I try to divide the campaign total calculation you created by the number of field visits per country I get a message that say that no further aggregation can be made.

• ###### 8. Re: How do I display the grand total for averages when using a table calculation?

Hi Julie,

I'm pretty queued up at the moment, I'll have time to take a look again within the next couple of days.

• ###### 9. Re: How do I display the grand total for averages when using a table calculation?

Thanks Jonathan, I appreciate it.

• ###### 10. Re: How do I display the grand total for averages when using a table calculation?

Hi,

Is Sheet5 what you are looking for?

Jonathan

• ###### 11. Re: How do I display the grand total for averages when using a table calculation?

Jonathan, this looks great, thank you!

• ###### 12. Re: How do I display the grand total for averages when using a table calculation?

Hi Jonathan,

I have a question regarding grand total, when I am  applying  table calculation its not giving me grand total,

This is the calculations == > 1) ZN(SUM([sales])) - LOOKUP(ZN(SUM([sales)),
-1)  [with the  Respect of  quarters]  2) ZN(SUM([profit]))
- LOOKUP(ZN(SUM([profit)), -1)

This is the calculations == > 1) ZN(SUM([sales])) - LOOKUP(ZN(SUM([sales)), -1)  [with the  Respect of  quarters]  2) ZN(SUM([profit])) - LOOKUP(ZN(SUM([profit)), -1)