10 Replies Latest reply on May 17, 2017 11:18 AM by Noel Castillo

Getting incorrect Grand Total for Calculated field

Hi All,

I am facing issue while calculating grand total for calculated field (using Table Calculation). Like in below given example, i am not getting correct grand total for column named 'Total available Hours'.

Here, I am using COUNTD in Calculation. And, I believe, this is because of common tasks b/w Name A,B & C

Is there any way of tweaking this in order to get correct summation for calculated field. So that, I can see some actual sum of Total available hours (1983+1275+708 = 3966)

Whereas, grand total is showing average for Average Handling Time as 11.91 which is correct based on our calculation.

Looking forward to get response soon

Many Thanks!!!

Pankaj

• 1. Re: Getting incorrect Grand Total for Calculated field

Hello Pankaj,

Here is a link that provides a lot of detailed instructions on Grand Totals, what causes these issues, and how to resolve them

Grand Total | Search Results | Drawing with Numbers

Credits to Jonathan Drummey

Regards,

Rody

• 2. Re: Getting incorrect Grand Total for Calculated field

Thanks for the ping, @Rody!

@Pankaj, before you go too far down the rabbit hole of customizing grand totals there can often be some easier solutions (such as LOD expressions for getting an embedded COUNTD that then gets SUM'ed in the grand total). If you can post a packaged workbook with some sample data we can take a look.

Jonathan

• 3. Re: Getting incorrect Grand Total for Calculated field

Click on the 5,950 displayed in the total line and from the tooltip click Automatic and change it to use Sum.

• 4. Re: Getting incorrect Grand Total for Calculated field

Thank you so much Jonathan Drummey, I too believe, sharing a packaged workbook with you before going through the detailed discussion on Customized Grand Totals will be easier to me

Workbook is attached here for your reference.

Many thanks to you as well Rody Zakovich for sharing this link !!!!!!

• 5. Re: Getting incorrect Grand Total for Calculated field

Thanks for your response @Brent !!

Actually, I have already tried this Automatic to Sum tip, but unfortunately it couldn't work for this particular column named 'Total Available Hours' whereas, it worked for few columns.

• 6. Re: Getting incorrect Grand Total for Calculated field

Hi Pankaj,

You just posted the Excel worksheet, not the Tableau workbook. Without being able to see your calculated fields I won't be able to help you.

Jonathan

• 7. Re: Getting incorrect Grand Total for Calculated field

I am extremely sorry Jonathan Drummey, just forgot to attach this. Kindly find the same now

Many Thanks!!!

• 8. Re: Getting incorrect Grand Total for Calculated field

A couple of questions after a quick inspection:

1. The Total Available Hours (by Rep) has two nested table calculations that each have a Compute Using of Table (Across). These aren't doing anything because there are no dimensions on Columns, you can get rid of the table calculations and get the same result. Is that what you want?

2. The formula for Total Available Hours/Month (by Rep) is:

(COUNTD([Name])*(([Total Distinct Months MAX])*141.6667))

In the detail rows COUNTD(Name) returns 1, in the Grand Total it will return 3 in the given workbook. Is that what you are expecting?

2. I packed all the calculated fields into a single formula:

(COUNTD([Name])*((WINDOW_MAX((COUNTD(str(year([Actual Month]))+str(month([Actual Month]))))))*141.6667)) / WINDOW_COUNT(countd([Complexity]))

Are you going to want each of these COUNTDs to be summed in the Grand Total or something else?

Jonathan

• 9. Re: Getting incorrect Grand Total for Calculated field

Yeah, Total Available Hours (by Rep) has two nested table calculations i.e. WINDOW_COUNT(countd([Complexity ])) & WINDOW_MAX([Total Distinct Months]). Actually, I am showcasing these values on another Workbook.

And, I believe, it should not affect the result for 'Total Available Hours (by Rep)'.

Secondly, yeah I am expecting the same result against COUNTD(Name) as mentioned by you. And, I guess, we both are correct till now as I am getting this result at my end as well.

Yes exactly, I am looking for the Calculated field to be summed up in Grand total (i.e. simple mathematical summation) like 10+20+15= 45

Also, Thanks for providing the single formula for this calculated field, I have tried this formula but it didn't reflect the previous values (Grand Total coming out to be same as earlier)

• 10. Re: Getting incorrect Grand Total for Calculated field

I'm having the same problem where 10 and 20 is a calculated field (based on your sample 10+20+15= 45).

Have you figure out a way how to solve this?

Thanks!