2 Replies Latest reply on Nov 2, 2016 5:51 AM by David Lu

    data hierarchies and totals

    David Lu
    David Lu Nov 1, 2016 3:23 PM

      My data source originally looked like this:

       

       

      For some of the visualizations I want to show, a hierarchy of Code/Label would be helpful, so I went ahead and created a couple more columns in my data:

       

      The issue I'm running into is when I pull this into Tableau and create a hierarchy for Level 1 Parent, Level 2 Parent, Level 3 Parent, and Label, Tableau aggregates the data automatically at the highest level and it is incorrect.  For example, if we just look at Level 1 and 2 for the Dept of Agriculture below, the row labeled N/A shows the correct total percentages for the Dept of Agriculture:

       

       

      However, if i take away Level 2 to just look at Level 1, Tableau is aggregating all the percentages and the results are incorrect.  This happens with every level.  How do I need to structure my data so that these aggregations don't occur?

        • 1. Re: data hierarchies and totals
          Trevor Toll
          Trevor Toll Nov 2, 2016 12:22 AM (in response to David Lu)

          So you have Level 2 = 'N/A' as your own calculated subtotal for all of Department of Agriculture? If that is the case then that is why the number doesn't match when you collapse up to the next hierarchy. I would remove your 'N/A' rows and just add a proper sub-total: Analysis > Totals > Subtotals. And if you want the total to appear on the top select 'Column totals to Top' as well.

          • 2. Re: data hierarchies and totals
            David Lu
            David Lu Nov 2, 2016 5:51 AM (in response to Trevor Toll)

            Hi Trevor, thanks for your suggestion but I actually want the opposite of that.  The N/A (or in this case subtotals) rows that appear after expanding are the numbers I want.  When I collapse the rows, Tableau aggregates the percentages below by averaging them and that's what i don't want.  I want to collapse the rows and have the N/A subtotals show as the correct numbers.