Span of Control

[PauloH]

Hi all,

I have a table structured like this:

Employee	Manager
1
2	1
3	1
4	1
5	2
6	2
7	2
8	2
9	2
10	3
11	3
12	3
13	3
14	4
15	4

I'm trying to implement a calculated column for Span of Control, basically count the number of occurances on Employee in the Manager column, like this:

Employee	Manager	Span of Control
1		3
2	1	3
3	1	4
4	1	0
5	2	0
6	2	0
7	2	2
8	7	0
9	7	0
10	3	0
11	3	0
12	3	2
13	12	0
14	12	0
15	3	0

In Excel, formula goes like COUNTIF(Manager Range, Employee), quite easy, however, no DAX formula variations I've tried (COUNTX, CALCULATE) has yielded the result I need. Any idea? 

[Smauro · Accepted solution]

You could simply add a new column with DAX:

Span of Control =
VAR x = [Employee]
RETURN
    CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', x = [Manager] ) )

You should change the 'Table' with your table name.

Edit: You may add a " + 0" after the "COUNTROWS(TABLE)" in order to not display null results

[tdemoss]

I was battling with this same issue and this worked for me as well!  Thank you!!!

[wwhittenton]

Assuming your employees don't repeat, @Smauro gave a better solution than I did. Kudos to him.

[Smauro]

@wwhittenton you are right, thank you!
I took it for certain that the employees would be unique.. If not, then your solution works better.

[wwhittenton]

@Smauro In retrospect, that's the better (or perhaps only viable) assumption. If the employees are not unique, the span of control calculation comes out incorrect either way, because the manager an individual reports to would be double-counted even in the new table.

Thanks for the good wishes and happy holidays to you and to you as well, @PauloH.

[Smauro]

@wwhittenton I think you're right. And there may be some people with more than one manager as well.
@PauloH So, in case your Employee-Manager has duplicates, you should first go on Query Editor, Select both Employee and Manager columns and right click-> remove duplicates. And then, add the column I mentioned.

Happy holidays to you too @wwhittenton! And @PauloH 🙂

[PauloH]

Hi @Smauro, any idea of how could this work in case there're duplicates? i.e. having different snapshot periods (months), hence getting some duplicates in the Employee and Manager column.

[wwhittenton]

Hi @PauloH,

Since you said you have snapshots, I'm assuming you want the most recent manager for a given employee (they could change over time). I built that into the calculation. This will require a new table, achieved by going to the Modeling tab and selecting "New Table". Then, use the following:

Span of Control = SUMMARIZE( Sheet1 , Sheet1[employee] , 
"Date" , MAX(Sheet1[Date]) ,
"manager" , calculate(max(Sheet1[manager]),
FILTER(Sheet1 , Sheet1[Date] = MAX(Sheet1[Date]))))

This will return a distinct list of employees with their respective manager for the most recent time period. Once that's done, you can follow the code you used originally in an additional column:

SpanOfControl = 
var x = 'Span of Control'[employee]
return
CALCULATE( COUNTROWS('Span of Control') , FILTER( 'Span of Control', x = 'Span of Control'[manager] ))

I hope this helps!

Best,

Will

[PauloH]

Hi @wwhittenton, it could be a bit more complicated, as users should be able to filter by dates. I'll use below as input and see how I can make it work. Thanks! 

[PauloH]

Thanks, all! Just tested the solution and it seems to work exactly as I intended! Happy Holidays!

[wwhittenton]

Hi @PauloH,

This may not be exactly the way you wanted to do it, but it should work.

Create a New Table by clicking on the "New Table" button in the Calculations section of the Home tab. then use the formula

Employee = DISTINCT(Data[employee])

 After that, create a New Column and use the following:

Span_of_Control = CALCULATE( COUNT(Data[manager]) , FILTER(Data , Employee[employee] = Data[Manager] ))

Remember to change the table / column names as needed. If you need to use filters or slicers in your data display, you'll be able to create a One-to-Many or Many-to-Many relationship to make them work correctly.

Hope this helps!

Will