Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Org Shaping Logic

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 ...
Need Help
  • freder1ck's avatar
    freder1ck
    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 BluegrassJack Bauer
    Jack BauerMagilla Gorilla
    Magilla GorillaRainbow Brite
    Rainbow BriteMatt Tracker
    Iam MumraMagilla Gorilla
    Matt Tracker
    FluttershyMatt Tracker
    WhistleFluttershy

    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

freder1ck's avatar
freder1ck
Kudo Kingpin
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 BluegrassJack Bauer
Jack BauerMagilla Gorilla
Magilla GorillaRainbow Brite
Rainbow BriteMatt Tracker
Iam MumraMagilla Gorilla
Matt Tracker
FluttershyMatt Tracker
WhistleFluttershy

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      freder1ck
      Kudo Kingpin
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

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