Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
benhoward00
Frequent Visitor

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

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.

 

benhoward00_0-1617207939233.png

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

 

benhoward00_1-1617208055562.png

Figure 2

and the org chart is as per figure3

benhoward00_2-1617208115782.png

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.

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
big_pall
New Member

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 

 

big_pall_0-1652873495009.png

 

Thank you in advance.

 

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.

OwenAuger_0-1652876210328.png

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

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.

OwenAuger_0-1652964693023.png

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.