HR/Employee Reporting: Using Paths & Hierarchies

Greetings everyone! PowerBI Storytime is a weekly newsletter with 4 data stories each month grouped around a particular reporting field.

Our February stories will shed some light onto HR and Employee Reporting walking you through the different scenarios, isssues and solutions that are typical for this type of reports. Stay tuned and let's dive into our first story!

Let me introduce you to Amy who holds a managerial position at a large company and wants to use Power BI to communicate her team's performance and progress to the company's upper management.

Amy has two data tables at her disposal: DimEmployee and Sales linked on an Employee Key. First she spends some time to understand the structure of her data.

A typical Employee table usually has columns like EmployeeKey and ParentEmployeeKey or ManagerKey. From an Employee Table it is easy to see that there are several levels of hierarchy in Amy’s organisation.  

An EmployeeKey 1 has a manager with an EmployeeKey 18, an EmployeeKey 18 has a manager with an EmployeeKey 23 and etc.

There is also a Fullname column in this table with a name of each Employee, but how can Amy also get a manager’s name displayed?

To retrieve this information, Amy uses Lookups to create a new column.

LOOKUPVALUE (result_columnName, search_columnName, search_value, …, [alternateResult])

Lookupvalue function returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value.

The value of result_column at the row where all pairs of search_column and search_value have a match.                                                            

Some considerations while doing the Lookups:

• If there is no match that satisfies all the search values, a BLANK or alternateResult, if supplied, is returned. In other words, the function will not return a lookup value if only some of the criteria match.
• If multiple rows match the search values and in all cases result_column values are identical then that value is returned. However, if result_column returns different values an error or alternateResult, if supplied, is returned.

Now Amy can easily create a matrix with a hierarchy of employees and see the sales amount her team generated.  Yay! But wait...ONE thing is missing here. Amy’s sales belong to Brian..

Using the LookupValue function we can quickly show the sales for each person grouped by their manager's name.

But the downside is that Amy's sales are listed under Brian, the Head of Sales. Amy's Team made 14.8M and Amy herself 730K. We want to show all her Team sales, including her own..

So, let us take a look at the steps Amy needs to go through to achieve what she wants.                          

To add Amy's sales to the team she needs to do a quick IF-check. Amy sits at level 3 so she is interested in that level and those above 3 who will eventually be her Team members.  

If we're at levels above 3, ie. 4 and 5, then take the Person at Level 3 to be the Team Manager. Otherwise, take the person themselves.

Now Amy can finally report the numbers of her team correctly. By the same token she can report now on a number of sick leave days, holidays taken, hours booked by her team.

You can use PATH FUNCTION to figure out an employee’s distance to a Company Leader, Number of Direct Reports per Manager, Number of Managers sitting at a certain level of hierarchy, etc.

If you missed my article "Success Factors: How to query more than 1000 rows of a dataset" check it out under the link below.

See you next week!