I have realised that I haven't included the IDs in the workbook. Salesperson name can be used instead, they are both unique.
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.
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.
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!