Counting number direct reports and the number of all reports in an organisation's hierarchy

[benhoward00]

I've seen a few posts regarding the count of the number of child items in a hierarchy, but I'd like to take this a stage further.  

I have a simple table with the employee name, employee_ID, supervisor name and supervisorID, as per figure 1.

Figure 1

I'd like to be able to summarise the number of direct reports and the number of all the reports for an employee, so the data I want to calc is in Figure 2

Figure 2

and the org chart is as per figure3

Figure 3.

Ideally need to to create these as calculated columns in the table, rather than measures, as depending on the number of "all reports" I need to create a new column in the table.  Note that the IDs are textual in this instance.  For a full soln obviously I'd love measures aswell 😉 

A link to the example files can be found here

Thanks,  Ben.

[big_pall]

This is a further step to counting the number of all the reports. I am trying to add a filter to the original dataset and I want the count to change dynamically. For example, out of these 8 employees, 5 have participated in a survey which is aimed to understand the impact of an application (Impact = Low, Medium and High). If I add a slicer for Impact, and I select Medium, I want the count of all the reports for those employees who have responded to the survey as Medium 

Thank you in advance.

[OwenAuger]

Hi @big_pall 

Yes, that should be possible 🙂

Just to clarify the requirements, if you filter on a given set of employees as you've described, do you want to count the combined pool of employees who report (directly or indirectly) to any of the filtered employees?

For example, in the diagram below, if we filtered on the four "red" employees, would we want to count the 11 "green" employees?

In this example, the red and green groups overlap.

Regards,

Owen

[big_pall]

Thanks @OwenAuger for such quick response! 

In your diagram, I am assuming that the "reds" have participated in the survey out of all the employees in the tree structure and all the "reds" have selected "Medium".

Now, if I apply filter as "Medium", I want the count of all reports (direct or indirect) who have selected "Medium" in the survey. In your example, count of all reports for Cesar = 2(including himself), Drew = 1, Josh = 1(including himself), Hannibal = 4, Mary = 4. 

Hope this makes sense! Please let me know if you need more details.

Best wishes,

Sandy

[OwenAuger]

Thanks @big_pall  🙂

That clarifies things - I had misunderstood originally.

And yes, I had intended the "reds" were those with Impact = Medium that were filtered.

Just restating what we want to calculate to make sure:

1. First, we will apply a filter on the survey results (e.g. Impact = Medium) to give us a set of "Employees filtered by Survey Results"
2. Second, we will filter on a particular Employee from the hierarchy, and count the number of "Employees filtered by Survey Results" who fall under that Employee, or who are that same Employee

The tricky thing when writing this calculation is that we want to filter on the survey results (Low, Medium or High) at the same time as filtering on a particular Empoloyee from the hierarchy.

I have attached a PBIX with a suggested solution.

To make it work, I suggest:

1. Create an Employee dimension table, containing EmployeeID, SupervisorID, Employee Name, and a calculated column Employee Path.
   
   Employee Path =

   PATH ( Employee[EmployeeID], Employee[SupervisorID] )
2. Create a Survey table which contains at least EmployeeID and Impact, and relates to the Employee table on the EmployeeID column.
3. Create the below measure:

Count of Reports under selected employee(s) based on Survey Filter = 
-- For each Employee currently filtered in the Survey table
-- (ignoring filters on the Employee table)
-- retrieve the Employee Path
VAR SurveyEmployeesPath =
    CALCULATETABLE (
        SUMMARIZE ( Survey, Employee[Employee Path] ),
        REMOVEFILTERS ( Employee )
    )
-- Get list of Employees currently visible in Employee table
VAR VisibleEmployees =
    VALUES ( Employee[EmployeeID] )
-- Filter SurveyEmployee paths to those that contain at least one
-- visible employee
VAR SurveyEmployeesUnderVisibleEmployees =
    FILTER (
        SurveyEmployeesPath,
        NOT ISEMPTY (
            FILTER ( VisibleEmployees, PATHCONTAINS ( Employee[Employee Path], Employee[EmployeeID] ) )
        )
    )
-- Count SurveyEmployees that meet the criteria
VAR Result =
    COUNTROWS ( SurveyEmployeesUnderVisibleEmployees )
RETURN
    Result

In summary, this measure:

1. Grabs the Employee Path for each Employee in the Survey table (subject to the Impact filter, but ignoring filters from the Employee table).
2. Filters those Employee Paths to just those containing the currently filtered Employee(s) from the Employee table.
3. Counts the resulting Employee Paths.

The result in my sample PBIX looks like this. If we filter on Impact = Medium, then the measure counts the Employees with Impact = Medium who are at or below a given Employee in the hierarchy. So, for example, Mary's result is 4 and Cesar's result is 2.

It's a little more complicated than I anticipated but this seems to work.

Is this what you were expecting, and does something like this work in your model?

Regards,

Owen

[big_pall]

Hello @OwenAuger ,

I had to tweak some table connections and manipulate the column measure, but the count measure works as expected. Thats a good news 😊 

Thanks for all the support. This community is growing pretty fast because of folks like yourself. Really appreciate your efforts. Thanks again!

Best wishes,

Sandesh

[OwenAuger · Accepted solution]

Hi @benhoward00 

Yes you can certainly created calculated columns for # directs and # all.

Since you have a parent-child hierarchy, you can make use of the handy PATH function.

1. To make the PATH function work, any blank SupervisorID values need to be replaced with null values (rather than the empty string). I did this by replacing "" with null in Power Query.

2. Create an Employees[Employee Path] calculated column:

Employee Path = 
PATH ( Employees[EmployeeID], Employees[SupervisorID] )

The PATH function in this case returns an ordered, vertical bar-delimited list of Employee ID values in the "path" from top-level to a given employee, e.g. for Janet the path is "0001|0003|0002"

3. Create Employees[# directs] and Employees[# all] calculated columns:

# directs = 
VAR CurrentEmployeeID = Employees[EmployeeID]
RETURN
CALCULATE ( 
    COUNTROWS ( Employees ),
    ALL ( Employees ),
    Employees[SupervisorID] = CurrentEmployeeID
)

# all = 
VAR CurrentEmployeeID = Employees[EmployeeID]
RETURN
CALCULATE ( 
    COUNTROWS ( Employees ),
    ALL ( Employees ),
    PATHCONTAINS ( Employees[Employee Path], CurrentEmployeeID ),
    Employees[EmployeeID] <> CurrentEmployeeID
)

The logic for # directs is that all direct reports must have the current row's EmployeeID as their SupervisorID.

The logic for # all is that a given EmployeeID appears in the path of all that employee's direct/indirect reports, and we just have to exclude that EmployeeID itself to get the correct count.

Updated PBIX attached.

Regards,

Owen

[benhoward00]

Thanks Owen, works a treat, I appreciate the help.