The following blog by Jacqui Moore was originally published on Do Mo(o)re With Data July 1, 2022 and is cross-posted here with permission. Jacqui Tableau Social Ambassador and a Senior Data Analytics and Viz Consultant for Cleartelligence.
This post describes the first of two methods of creating an Org Chart in Tableau. If you haven’t read the post where I give a short summary of the pros and cons of each method, you can find that here. If you would like to download the data, and follow along, you can download my files to follow along here.
In order to create an org chart (or any hierarchy chart or dendrogram) you will need to establish the relationship between each employee and every level of the hierarchy above them, as well as to their own direct reports.
To do this, we will create a row for every relationship. One for each employee-supervisor, all the way up to the CEO, and one for each employee they directly manage.
We will start, as these things usually do, with data. You will need an employees table containing at least the employee’s name, and the name of their manager. Preferably, it will have IDs. It may also have other information about the employee. It will probably look something like this:
For this method, we will end up with a data structure like this:
This will create all of the points we need to build the org chart.
Let’s get started…
The Data Prep
First, we will handle the supervisor hierarchy. This is done using a series of self-joins.
Below, I walk through how this can be done in Tableau Prep because it breaks down the steps clearly. You can also do this in Alteryx or SQL. Ideally, you would use a batch macro (Alteryx) or recursive CTE (SQL), to allow your model to automatically scale with the organization.
Connect to the employees table.
Add a cleanse step to remove all fields except for [Employee ID], [Manager ID], [Employee], and [Manager]. This will make it easier to manage as we build the flow.
Next, we will perform a self-join. Add a cleanse step, and left join the employees table to this cleanse step on [Manager ID] = [Employee ID]. This will bring in the 2nd level manager.
Rename fields: [Manager ID] becomes [Manager ID L1], [Manager] becomes [Manager ID L2] and Remove duplicate fields [Employee] and [Employee ID]
Left Join the employees table again, this time on [Manager ID L2] = [Employee ID]
Rename fields: [Manager ID] becomes [Manager ID L3], [Manager] becomes [Manager ID L3] and Remove duplicate fields [Employee] and [Employee ID]
Left Join the employees table again, this time on [Manager ID L3] = [Employee ID]
Rename fields: [Manager ID] becomes [Manager ID L4], [Manager] becomes [Manager ID L4] and Remove duplicate fields [Employee] and [Employee ID]
Continue to do this until the newly added [Manager ID] field is null for all records. Check your record counts and results as you go through the process to ensure you have the same number of records you started with.
Create a new calculated field called [Relationship] and type ‘Supervisor’ in the calculation box. We will use this downstream.
Create a field called [Path Order]. This will be used for sorting and placement of the items on the chart.
IFNULL([Manager_L4],'')+IFNULL([Manager_L3],'')+IFNULL([Manager_L2],'')+IFNULL([Manager_L2],'')+[Employee]Next we will pivot the managers from columns to rows, creating a record for each manager level above the employee. Using Tableau Prep, you can pivot both fields simultaneously, and use a wildcard to get all of the fields without manually selecting them.
Rename the fields to [Manager Level], [Relation ID] and [Relation], as above.
From the new [Manager Level] field, remove letters and punctuation. Change the data type to numerical.
Create a calculated field called [Manager Level] with the following logic. What we are doing here is cleansing this field. We want to assign level 0 to the CEO, and NULL any where the level is not applicable.
IF [Manager Level]=1 AND ISNULL([Relation_ID]) THEN 0
ELSEIF [Manager Level]>1 AND ISNULL([Relation_ID]) THEN NULL
ELSE [Manager Level] ENDFilter out any records where the [Manager Level] = NULL
At this point the workflow looks something like this:
Next we will create a branch of the flow for direct subordinates. Create a new cleanse step, and left join the employees table to this cleanse step on [Employee ID] = [Manager ID].
Clean up the data.
Remove both [Manager ID] and [Manager] fields
Remove the [Employee] field that was brought in from the second cleanse step
Rename the [Employee ID] field that was brought in from the first cleanse step to [Relation ID]
Rename the [Employee] field that was brought in from the first cleanse step to [Relation]
Create a calculated field called [Relationship] and type ‘Subordinate’ in the calculation window
Filter to exclude [Relation] = NULL
After this, we will have two branches. One with all supervisors, and one with the direct subordinates. If you’re confused at this point, I don’t blame you, and I recommend downloading the Tableau Prep flow.
Union the supervisor branch and the subordinate branch. Remove the [Table Names] field.
Now we just need to clean up some nulls from the Subordinate stream
Create a FIXED LOD called [Order]. Right click > Create calculated field > FIXED LOD. Group by is [Employee ID] and Compute Using is MAX [Path Order]. Remove [Path Order].
Create another FIXED LOD, this one is called [Level]. It will take the number of managers a person has to calculate their level in the organization. Group by is [Employee ID] and Compute Using is MAX [Manager Level].
Finally, inner join the original table on [Employee ID] = [Employee ID]. Remove the new [Manager], [Manager ID], [Employee ID] and [Employee] fields.
Output your data
The final workflow will look something like this:
Building an Interactive Org Chart in Tableau Desktop
Now that we have the data shaped for the dashboard we want to build, let’s build the dashboard!
Place [Level] on Rows. This will place employees in rows based on the hierarchy.
Create a calculated field named [Position] = (INDEX()+1)/(SIZE()+3).
Place [Position] on Columns. This field will be used to evenly distribute the marks on the view.
Create a calculated field named [Path]. This field will be used to draw the lines between employees.
IF [Relationship]="Subordinate" THEN [Employee]+'-'+[Relation]
ELSEIF [Relationship]="Supervisor" THEN {FIXED [Employee],[Relationship]: MAX( IF [Manager Level]=1 THEN [Relation]+'-'+[Employee] END )}
ENDCreate a combined field from Level & Employee. (If you’re like me, you hadn’t heard of combined fields before this… Click [Level], hold Ctrl + click [Employee]. Right click, and navigate to Create > Combined field.
This will create a new field called [Level & Employee (Combined)].
Next we create the view level of detail. Place [Level & Employee (Combined)] on Detail.
Don’t worry — It still isn’t supposed to look like anything.
Reverse the [Level] axis. Right click on the axis, and check “Reverse”
Now we need to edit the table calculation for the [Position]. Right click [Position], and go to “Edit Table Calculation”
Under “Compute Using”, select “Specific Dimensions”. Using the combined field we created earlier allows us to use both fields in the table calculation. [Employee] should be selected, [Level] is left unchecked.
Now the view is starting to look like something!
Next, we will create a dual axis by duplicating the MIN(Level) on Rows.
Set it to dual axis, and synchronize the axes.
Go to the Marks card for the new axis, and change the mark type to “Line”. Place [Path] above Level & Employee (Combined) on the card.
Edit Table Calculation, and select Employee and Path. At the Level should be set to “Employee”, and Sort Order should be set to [Path]. This will place the employees under their manager.
Place ATTR(Employee) on Label. Set the label to Top Center, and uncheck allow labels to overlap other marks.
Create a parameter called [Selected Employee]
Create a calculated field called [Filter Selected Employee]
//Employee
[Selected Employee] = [Employee]
OR
//Manager
[Selected Employee]= IF [Relationship]='subordinate' THEN [Relation] END
OR
//Direct Reports
[Selected Employee] = {FIXED [Employee]: MAX(IF [Manager Level] =1 THEN [Relation] END)}
Place this filter on the view, and select “True”
Edit the “Position” axis to not include zero.
Hide all of the axes. and place the view on a dashboard. Once it is on the dashboard, we will use a parameter action to allow us to navigate the view.
Dashboard > Actions > Add Action > Change Parameter
Source Sheet should be your org chart. Target Parameter is Employee. Source Field is Employee.
Now, the selected employee will always be the center, but clicking the manager or subordinates will navigate up or down the org chart
Boom. You just built an interactive org chart.
Don’t forget the formatting!
Use Brian’s highlight trick to get rid of the default highlighting
Make the lines lighter and thinner to give visual priority to the names
Add an indicator to the circles to let users know if there is something to drill into
Format the labels
Change the tooltips
Put some branding on the dashboard
Add the parameter to the view to allow users to select an employee
Set it to the CEO (or top of your hierarchy) before publishing
You can find my finished version on Tableau Public.