I'm sorry but I can't understand what you really want to do...
There is a simple way to show subtotal and total.
Then you can see the subtotal of each category and grand total across all categories together.
If you feel the above is not enough solution, would you tell me the goal image with excel mock-up or something like that?
Then I probably can tell you more information...
I only want the arrest types to show one category (group) at a time rather than all together, which is why I am filtering them using Category. For all the category options except grandtotal (i.e.,Drug, Property, Society, Violent), the table is doing what I want and showing the arrest counts for each arrest type within the chosen category, plus the subtotal for that category. But when the user chooses the grandtotal option from the filter, what they see is two columns of the exact same values - one column because the data are already summed across the other categories in my dataset and one column because of the Show Row Grand Totals I applied to the entire table. I want the Tableau subtotals to be hidden/suppressed/turned off only in that situation - when grandtotal is the chosen Category from the filter.
Ideally, we want the grand total to show all by itself without any of the other arrest types, which is why I included it in the dataset - so it could show as its own group. We also have a few other filters that affect the grand total - year and adult vs. juvenile (Year was set as a filter in my sample workbook). If there is a better way, please share! If no one has a solution, my plan is to make a separate dashboard page devoted to the subtotals and grand total, but that's not ideal.
I want the table to look like this when grandtotal is chosen as the Category from the filter:
1 of 1 people found this helpful
I see what you want to say.
If you can allow to use the parameter it can be solved.
Create the calculated field to select which category is to show.
IF [Category]=[Category Parameter] THEN 'SHOW' ELSE 'HIDE' END
And drag it into Columns, and then hide 'HIDE' like this.
Now you can get the subtotal of displayed category and grand total of across all of the categories.
Even if the data doesn't have the 'grandtotal' row.
Because Total columns are not affected by Hide.
It is a little bit tricky but would be helpful for you some more.
Thank you Takaaki! This led me to what I needed. I created a string parameter "Total" with only one value: grandtotal (the total from my dataset). Then I altered your calculated field formula slightly, to: IF [Category]<>[Parameters].[Total] THEN 'SHOW' ELSE 'HIDE' END.
Then I put the calculated field on the Columns shelf and hid it as you instructed. I am able to use Tableau's subtotals with the grand total from my dataset and the totals only show once in the data table if GrandTotal is selected from the filter. Success!
Jen, are you able to post what your dashboard so I can see what you did? Looking to do something similar.