Tableau has the ability to turn on totals and sub-totals, so they really shouldn't be in the source data. Once I removed them from the source, it was fairly easy to get a decent looking table. You will notice that Tableau always places sub-totals and grand totals at the bottom.
There are a couple of other things you might take into account on the source data:
- The current structure (without embedded totals) works well for a single table, but doing any further analysis might be difficult because you have two measures in one field. Ideally, "Income" and "Population" would be two separate columns in the Excel file so that you could have two different measure fields to work with.
- Having said that, the level of detail or granularity is different between the two measures. For population it is at the gender level. For income it is at a ethnic group level. You probably would really want two source tables in this case: one for population and one for income. Each table would be at the correct level of granularity.
In the attached workbook (see image below), I turned on sub-totals by right clicking the [Item] field on Rows and checking "Subtotals". I adjusted the formatting a little to make them stand out.
Sub-Totals.twbx.zip 49.5 KB
Thanks much Joshua Milligan
an additional question on this theme: what would you recommend as a file structure to model a tree-like hierarchy that has 1-n levels of granularity (where max depth of the tree is probably on 4 or 5 levels) and not every root node has the same number of sublevels. I am trying to arrange a dataset and make a table in Tableau that looks like this:
The obvious way seems to be to have N columns in my source data file that each represent a dimension but I have to hardcore the number of levels which is not great. attached xls contains what i mean.
Level1 Level2 Level3 Level4 Value
Thing1 Thing2 18
Thing1 Thing3 22
Thing4 Thing5 4
Thing4 Thing5 Thing6 12
Thing4 Thing5 Thing7 3
Thing4 Thing5 Thing7 Thing8 19
Tree.xls 17.5 KB
To follow on, I found something that is basically a perfect illustration of what I am trying to do in Tableau.
Please have a look at the table shown in the link below (Financial Table). The key thing about this table is its ability to go pretty deep in the nesting of groups (6-8 no problem) and render it well even if the row labels are 20-30 characters. Also you can expand or selectively collapse each group, something that i have not been able to do in Tableau either. Finally, it does not do any math when you expand collapse.
In Tableau I have tried to use Hierarchy but it does not seem designed to build this kind of table, which is quite important to my specific use case. The table I am trying to build looks like the one in the link, and I will have 100 - 150 rows and 20-45 columns.
Any help is much appreciated!