The following post is a guest post by Josh Weyburne, Enterprise Sales Consultant for Tableau Software.

2/27/2017
Building an Organizational Chart in Tableau

Challenge: With nothing more than a list of employees and their manager, how do you determine the organizational structure and create a hierarchy diagram in Tableau?




First, you need to determine the 2nd level manager for each employee. To get the 2nd level manager, you can do a self join where “Supervisor” from the first table (Simple Example) equals “Employee” from the second table (Simple Example1). This means that the field “Supervisor” from the second table now represents the 2nd level manager for the “Employee” field in the first table. If we hide “Employee (Simple Example1)” and rename “Supervisor (Simple Example1)” to “Supervisor – L2” we get the following result.




To get the 3rd level leader, this process can be repeated. Now the “Supervisor – L2” field from the 2nd table (Simple Example1) can be joined with the “Employee (Simple Example2)” field from the 3rd table. Again, hide “Employee (Simple Example2)” and rename “Supervisor (Simple Example2)” to “Supervisor – L3” we get the following result.

This example data only has 4 levels so “Supervisor – L3” is the head of the company. If you are unsure how many levels exist, you can just repeat this process until all the fields in the “Supervisor” field are null.




Filtering out the null values for “Supervisor – L3”, we can see the organizational structure by building a quick crosstab. Now we can start building the network chart.




One way to think of a network chart is a set of x and y points that are connected with lines based on the defined hierarchy. If each employee is a unique point, the question then is how can we can extrapolate what the x and y position of each employee should be. We will start with determining the y position first. In our example below, the highest level manager should be on top of the chart. We want to give Wallace an y value of 4, Dolan and Tucker and y value of 3, and so on.




If we look at the crosstab below, we can see that Wallace is the only employee with a null value for “Supervisor” and Tucker and Dolan are the only ones besides Wallace with a null value for “Supervisor – L2”. This allows us to write the following formula to properly assign y values for every employee.




Now we need to determine what the x position. One way we can determine the x position is try and evenly space and center all the employees. For Wallace, since he is the only employee at tat level, he should be in the middle. For Dolan and Tucker, since there are 2 employees at that level, they should each be placed at 1/3 and 2/3 of the distance respectively. This pattern continues where each employee should be placed at a spacing of 1/(total employees at that level). The INDEX() function can be used to determine relative position of each employee and SIZE() determines how many employees are at each level. By adding 1 to the SIZE() we ensure the employees are centered.




The x formula will ensure the employees are properly spaced but we now need to tell Tableau how to sort each row so that employees end up directly below their manager. If you look in the view below, you can see the last rows of the two viz’s don’t match up. To fix this, we can make a calculation that creates a string with each employee’s management chain. This field then can be used to do an alphabetic sort ensuring that employee’s line up under their manager. Adding a sort by “Employee Sort” to the table calc allows the INDEX() function to properly sort the employee’s at each level.




Next we need to draw the lines between the employees and their managers. To connect two points in Tableau, there are two minimum requirements. First you need 2 rows of data for each line you want to draw and second, you need a unique key that identifies each line. Because every person in middle management not only needs a line drawn below them to their employee but also above them to their manager. This means we need to duplicate the first data set and this can be done by doing a self union.




To create a unique key that identifies each line, we will use a combo of the “Employee” and “Supervisor” fields. Because the level of detail is defined by the employee field, in order for a manager to have a line connected to both their manager and their employee, we need to determine what subordinates every manager has. To accomplish this, we can do another self join but this time joining the “Employee” field in Simple Example+ table with the “Supervisor” field in the Simple Example4 table. We can now hide the “Supervisor (Simple Example4)” field and rename “Employee (Simple Example4)” to “Subordinate”.




We can now create the unique key that identifies each line by using the following formula. Note, the field “Table Name” was added when we performed the union. When using the Simple Example table, we just use the “Employee” and “Supervisor” field. When we use the duplicated table, we want to use the “Subordinate” and “Employee” fields.




In order to draw lines between each employee, the lowest level of detail needs to be at the “Employee+Supervisor” level. However, to properly calculate the “X” field, you need to be able to address the “Employee” field but reset every level of “Y”. Adding “Employee” to the view gives you to granular of data to draw the lines but not having it won’t allow you to properly calculate “X”. To solve this, you can create a combined field with “Employee” and “Y”. Combined fields are not materialized as a single field so they don’t effect the level of detail but allow you to use them in the table calc.




You can now make the hierarchy chart by:
   • Adding “X” to columns
   • Adding “AVG(Y)” to row. Note that “Y” needs to be set as a dimension so it can be included in the “X” table calc.
   • Adding “Employee+Supervisor” and “Y&Employee (Combined)” to detail
   • Filter null values of “Employee+Supervisor”
   • Edit table calc for “X” to do the following. Note, the order of “Employee” and “Employee+Supervisor” matters.





After some minor formatting, the final result will look something like this:

Click on the image below to see the Tableau Public public version or download the Tableau workbork here.





Note: This article was written before Tableau adapts its logical join feature (or we may call nooodle relationship). In this blog post, Josh joined the data on top itself, once for each level of data. However, you can still double-click on noodles to switch back to class joins if needed.

I hope you find this information helpful. If you have any questions feel free to email me at Jeff@DataPlusScience.com

Jeffrey A. Shaffer
Follow on Twitter @HighVizAbility

Josh Weyburne
Follow on Twitter @JoshWeyburne.