Here is a link that provides a lot of detailed instructions on Grand Totals, what causes these issues, and how to resolve them
Credits to Jonathan Drummey
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.
Click on the 5,950 displayed in the total line and from the tooltip click Automatic and change it to use Sum.
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 !!!!!!
Data.xlsx 27.4 KB
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.
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.
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?
Hey Jonathan Drummey
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)
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?