3 Replies Latest reply on Oct 31, 2013 4:11 PM by gioel.molinari

    Create nested measures in a table like an income statement

    gioel.molinari

      Am trying to do something relatively simple in a table: create nested measures such that there can be groupings of measures and breakouts into component line items. Very similar in concept to displaying an income statement.

       

      I don't need Tableau to calculate anything at the rolled up measure level because I am feeding in the calculated values and I am using a custom aggregation that I would like to just handle outside tableau in my data prep engine, but I dont know how to do this exactly. I have attached an excel file that shows what i would like like table to look like with the collapsible rows and the closest I have gotten on the data modeling side and Tableau implementation.


      The basic problem I have is in producing the Totalization / parent items in the table .. I think i must be missing something basic.

       

      Workbook: Nesting

       

      Thanks

        • 1. Re: Create nested measures in a table like an income statement
          Joshua Milligan

          Gioel,

           

          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.

           

          Regards,

          Joshua

           

           

          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.

           

          0.png

          1 of 1 people found this helpful
          • 2. Re: Create nested measures in a table like an income statement
            gioel.molinari

            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:

             

            Thing1

               Thing2

               Thing3

            Thing4

              Thing5

                 Thing6

                 Thing7

                    Thing8

             

            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                                                 32

            Thing1     Thing2                                   18

            Thing1     Thing3                                   22

            Thing4                                                  8

            Thing4     Thing5                                   4

            Thing4     Thing5     Thing6                    12

            Thing4     Thing5     Thing7                    3

            Thing4     Thing5     Thing7     Thing8     19

            • 3. Re: Create nested measures in a table like an income statement
              gioel.molinari

              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!

               

              http://addepar.github.io/#/ember-table/financial