Org Shaping Logic

[Anonymous]

Hi Folks,

A little stumped here. My company has a document that shows reporting relationships up to 6 layers. What I want to do is to create some of these layers as drop down filters and to show some org breakdowns. Below is an example of the data:

EMPLOYEE_NAME

EMPLOYEE_NUMBER

Mgr1

MANAGER_LAYER_1_EMP_NUMBER

Mgr2

MANAGER_LAYER_2_EMP_NUMBER

Mgr3

MANAGER_LAYER_3_EMP_NUMBER

Mgr4

MANAGER_LAYER_4_EMP_NUMBER

Mgr5

MANAGER_LAYER_5_EMP_NUMBER

Mgr6

MANAGER_LAYER_6_EMP_NUMBER

Bluegrass, Colonel

111111

Bauer, Jack

123456

Magilla, Gorilla

123654

Jetson, George

148173

Phooey, Hong Kong

140849

Brite, Rainbow

145286

Trakker, Matt

822

Mumra, Iam

111111

Magilla, Gorilla

123654

Brite, Rainbow

145286

Trakker, Matt

654321

For example, Colonel Bluegrass is an individual contributor and Jack Bauer is his manager. We then look at Jack Bauer. Magilla Gorilla is his manager and so on as we go across. In this case, Colonel Bluegrass is low on the totem pole and Matt Trakker is the top-most layer and Colonel Bluegrass should fall under Matt if I selected Matt's name. Additionally, I may need to do a drop down for Mgr5 (Rainbow Brite) and get all of the people under her. So if I wanted to see Matt Trakker's organization, I would want to see all of those people under him by selecting his name on a drop down. Same with Rainbow Brite. Does that make sense?

Here's where it gets sticky. Sometimes a person may not have 6 layers of management because that person's manager could be an SVP or even the CEO. In this case, we have Iam Mumra who reports to Magilla Gorilla, who reports to Rainbow Brite and rolls up under Matt Trakker. 

Now if I selected "Matt Trakker" from a dropdown, how would it know to pull Matt Trakker from Mgr6 field and Matt Trakker from Mgr3 field? Iam Mumra is a higher level than Colonel Bluegrass because Iam Mumra could be a Director and Colonel Bluegrass could be an individual contributor. 

Mgr 1 is the lowest level of management and Mgr 6 is highest. 

Any help would be great here. I'm just completely stumped. I need this to be dynamic because we have leadership and management changes all of the time. So I need it to reference fields, not physical names. 

Please and thank you!

[freder1ck · Accepted solution]

First of all, how can Gorilla Magilla have George Jetson and Rainbow Brite both as managers?

There was a great post on this today at PowerPivotPro.com, so I applied it using your situation.

You can generate an org chart if you have each employee's manager. 

The data should look something like this:

EmployeeManager

Col Bluegrass	Jack Bauer
Jack Bauer	Magilla Gorilla
Magilla Gorilla	Rainbow Brite
Rainbow Brite	Matt Tracker
Iam Mumra	Magilla Gorilla
Matt Tracker
Fluttershy	Matt Tracker
Whistle	Fluttershy

Then, you'll add calculated columns to the table.

The first column does error handling for the top manager: 

[New Manager] =
VAR CurrentEmployee = People[Manager]
RETURN

IF (
COUNTROWS (
FILTER ( People, People[Employee] = CurrentEmployee )
)
= 0,
People[Employee],
People[Manager]
)

[Manager Path]

=
PATH (
People[Employee],
People[New Manager] )

[Level 1] =
LOOKUPVALUE (
People[Employee],
People[Employee], PATHITEM ( People[Manager Path], 1 )
)

[Level 2] =
LOOKUPVALUE (
People[Employee],
People[Employee], PATHITEM ( People[Manager Path], 2 )
)

[Level x] =
LOOKUPVALUE (
People[Employee],
People[Employee], PATHITEM ( People[Manager Path], x )
)

The P3 article: 

https://powerpivotpro.com/2017/12/imagine-people-tables/

Yours,

Fred

[Anonymous]

Thanks @freder1ck. This is a huge help. I did make a mistake with Magilla Gorilla.

How does the 1013 | 1015 | 1211 get created? 

Colonel Bluegrass' immediate manager is Jack Bauer, but Matt Trakker is the CEO. If I clicked Matt Trakker, I would want to make sure everyone in the company rolls up under him. Matt Trakker could stop at Level 1, 2, 3, 4 or 5 depending on who the employee is.

So if I selected Rainbow Brite as a drop down option and she was level 3, would it pull everyone under her (her direct reports and everyone else under them)?

I'm trying to be able to make these drop down options to get snapshots of everyone under a certain manager. 

[freder1ck]

SirGalasti,

This will work if there's a lookup relationship to the employee org chart from the data. Then when you add slicers for Level 1, Level 2, etc., they will crossfilter each other and the data. 

Path() is the function which generates 1013|1015|1211.

Explanation here: https://msdn.microsoft.com/en-us/library/gg492167.aspx

(much better than the typical documentation, by the way).

I created a sample file based on answering this question. I'll see if I can get it uploaded for you tonight. 

Since you are mainly interested in seeing the org chart, I'll think about visualizations as well.

Fred

[Anonymous]

Thanks! That would be very helpful!! Much appreciated. I did get started on this myself and it looks promising!!

[freder1ck]

 For visualization, I have shown a set of tables with the various levels. It would also work with slicers.

[Anonymous]

Thanks, @freder1ck! I'm going to try this and see what happens. It looks like it has shot out some employees to 9 paths. That seems a bit much, but could very well be bad data in our systems.