Forum Discussion
Org Shaping Logic
- 8 years ago
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]
RETURNIF (
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
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
Thanks! That would be very helpful!! Much appreciated. I did get started on this myself and it looks promising!!
- freder1ck8 years agoKudo Kingpin
For visualization, I have shown a set of tables with the various levels. It would also work with slicers.
- Anonymous8 years agoNot applicable
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.