PowerPivot - Employees reporting to a Manager (recursively)

[Vijay777]

Hello I'm trying to generate a report in PowerPivot which recursively calculates the total number of employees reporting to a Manager. I have a typical Employee table as shown below.

If would like to filter by EmpID and the report should show me the count of the Employees reporting to the user grouped by the department. The Employee Count needs to be recursive to nth level rather than just 1 level.

[TABLE="width: 250"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]EmpID[/TD]
[TD]ManagerID[/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]Operations[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]A[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]A[/TD]
[TD]BackOffice[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]A[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]C[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]C[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]D[/TD]
[TD]BackOffice[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD][/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]H[/TD]
[TD]BackOffice[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]I[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]H[/TD]
[TD]Sales[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance

 

[Laurent C]

You would first create a calculated column:
[HierarchyPath] := PATH( [EmpId] , [ManagerID] )

Once done you can use the PATHCONTAINS function to check which employees are under the responsibility of the selected employees.

In measures, you will need a double row context, to be able to use it.

Here is a SUMX example:

=SUMX(
      VALUES(Employees),
           COUNTROWS( 
                  FILTER( ALL(Employees) , 
                            PATHCONTAINS( [HierarchyPath], EARLIER( [EmpID]))
                  )
        )
 )

Here is a GENERATE example:

=CALCULATE(  
 COUNTROWS( VALUES( Employees ) )
        ,SUMMARIZE(
   GENERATE(   VALUES( Employees[EmpID] )
   , FILTER(    ALL( Employees[HierarchyPath] )
    ,   PATHCONTAINS(  [HierarchyPath], EARLIER( [EmpID] )    )
   )
  )
                , [HierarchyPath]
        )
        , ALL(Employees)
  )

Note the sub-totals behave differently in each case.

 

[Vijay777]

Thanks for the reply. The Path function was taking too much time in excel as i had a large dataset so I had to generate the results using SQL Server and populate the excel sheet.

I wanted to add a filter by EmpID to a pivot report. I'm assuming that i would have to pass the EmpID as a parameter to the Calculated column which has the PATHCONTAINS function. Can you guide me how I can pass parameters? OR if there is a better way to achieve this filtering ability.

PS: My final goal is to create a Pivot Chart of Count of Employees reporting to a Manager grouped by Dept. I should be able to filter by selecting a manager.

 

[Laurent C]

Applying a filter in a calculated column requires a CALCULATE expression.

This creates an evalation filter with all filters set to the value in each column on the current row.

If you only want to filter on one column, then you have to use an ALL to remove other filters.

What kind of calculated column do you have in mind?

 

[Vijay777]

Basically this is my requirement:

1. User Selects an Employee (Manager)
2. Display the count of Employees Reporting to the Manager grouped by Dept
Here is the link to the excel sheet:http://sdrv.ms/10nK2hy You can download it if needed.

Implementation
1. Create a new column [MgrHierarchy] := PATH( [EmpId] , [ManagerID] )
2. Create a Pivot Table. In the Rows > Add column Dept In Values add Count of EmpID.
3. Now i'm stuck how can a user select a Manager. My implementation was to add the MgrHierarchy to the filter. Then a user would have to search in the MgrHierarchy for a EmpID and select all results. This would filter the pivot table below

I'm not sure if this is the cleanest approach. Also i would like for a user to Select a Name instead of EmpID.