4 Replies Latest reply on Aug 3, 2017 8:25 AM by Michael Gillespie

    Dealing with hierarchy




      In the attached workbook I have a salesperson hierarchy that I am having to manage in Tableau. Is anyone able to help me please?


      I want the following fields:

      A field to show the highest level in the hierarchy (Highest Level in Salesperson Hierarchy: I have achieved this already)

      A field to count all of the distinct subordinates based on the highest level in the hierarchy (Salesperson Hierarchy (Number of Subordinates): this isn't working currently). I want to be able to place this anywhere and for it to count the lowest level.

      A field to select all of the distinct IDs for the subordinates which can be used in the filter (Salesperson Id to use (test): this is not working)


      Hope this makes sense, thank you in advance.

        • 1. Re: Dealing with hierarchy

          I have realised that I haven't included the IDs in the workbook. Salesperson name can be used instead, they are both unique.

          • 2. Re: Dealing with hierarchy
            Michael Gillespie

            Gareth, you're going to have a very hard time doing what you want with your data in this format.  Ideally, you need a much "taller" data set.

            By that, I mean you need a NAME column, a TITLE/ROLE column and a way to indicate the hierarchy (something like "Who is this person's immediate boss").  That's the only way to I can see to get the hierarchy information you need to be able to count effectively.


            There are issues even with that approach (for example, Andrew Cole is both VP1 and Head Of - do we count him twice or once?) but you can likely figure out the rules for that problem.


            It MIGHT be possible to do this with a simple pivot on the Data Source tab.  I don't think it is: you don't have a single NAME column, so I don't think the pivot will get you what you need, but it might be worth a try just so you can see how it works.

            • 3. Re: Dealing with hierarchy

              Hi Michael, thank you for your message.

              I see what you mean about the need for a name column.

              I was hoping that the Highest Level in Salesperson Hierarchy field would give me what I need for finding the right level in the hierarchy. Rather than "Who is this person's immediate boss" I am trying to find the boss and then select their subordinates. Guess it is the same thing just from the different direction.

              I only want to count the distinct IDs/Salesperson names so it would only be one Andrew Cole in the example you gave.

              • 4. Re: Dealing with hierarchy
                Michael Gillespie

                In Tableau language, you need a distinct dimension that contains all instances of the thing you're trying to count/sum/average/etc.  In this case, that means you need a column that contains all the names of the people in the hierarchy. What you have today is multiple columns, or multiple dimensions, so you can't do any analysis on ALL the names in one place.


                Same for Role/Title.  You need a single column with that info in it.  That means you will have multiple rows for the same person if that person has more than one Role, but that's OK.  The compound key of Person + Role should be unique, which means you can (theoretically) count what you want to count.


                You should know that this is NOT a strong suit of Tableau - we struggle with org chart hierarchies in Tableau.  It's just hard to represent this kind of structure in a way that allows Tableau to count things.  It's relatively easy to create a PICTURE of an org chart, but anything beyond that is a series of tradeoffs that can be hard to wrestle with.


                Search the forums for "org chart".  There are lots of posts that can help!