Forum Discussion
Employee Manager and manager as employee
sundernt Have been coming back to this thread several times and would like to offer you two solutions, demonstrated in the attached file. First, you need to put your data in a structured table. Include all employees excluding the top manager with their "first level manager", in any particular order. It doesn't have be sorted in any way. Then, add a number of "next level manager" columns until no more managers are listed. The formulae should update automatically!
If you are on the latest version of Excel, you can use XLOOKUP and some of the other new functions to make the summary you seek. In case your version of Excel does not support these new functions yet, you may have a look at the alternative solution using VLOOKUP and only traditional functions.
I could extract the information in required format through PATH (Dax) in power pivot model and apply the logic you have mentioned. your timely help was extremely valuable. Thanks Again.
I just want to come in here with a question: where do you get the numbers in the first place of how many people report to each manager?
Having had a stint during my career as the director of the HR (and Payroll) database for a major corporation, having been involved in the design of our own highly relational database (using IBM's DB2 database software--this was in the 1980s) I became somewhat familiar with the notions of normalization, relational, one-to-many, many-to-many, and so on. (I was the director on the user-end of things, not the IT, although I did learn enough to be dangerous.)
Counting heads is always a challenge in itself, because there are so many ways to categorize employees, to begin with. The Pivot Table, when it came along, was such a boon, because you have have headcounts arrayed by organizational units (depts, divisions, etc) along the vertical axis, and employment classifications (F/T, P/T, Temp, etc) along the horizontal. I taught myself SQL and had fun extracting data from the DB2 database to produce monthly headcounts.
Creating a summary of reporting relationships has got to be one of the most complicated, however, because of precisely what you've identified here.
- You've got employees, each of whom has a job (and job code);
- each of whom (employees still as the subject here) is in a position;
- but it's each position that reports to another position (with the exception of the Pres/CEO). (because employees can be reassigned to different positions, different jobs, and positions can be moved around as well, distinctly and in groups.
So any snapshot today of reporting relationships is out-of-date tomorrow, even if the employee headcount hasn't changed, just by virtue of the fluidity of org structure.
- There's a one-to-one relationship between employee and position
- A one-to-many between employee and job code (for most job codes actually the case; in every job code, potentially the case)
- And a many-to-one relationship between subordinate positions and managerial position (although that's challenged in matrix organizations!)
I bring all this up just to say that a simple spreadsheet summarizing the data is probably the easy part. The hard part is getting those numbers that report in at each level of the org chart...and doing so reliably for any given snapshot.
