Forum Discussion
Span of Control
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
Solution Sagewwhittenton you are right, thank you!
I took it for certain that the employees would be unique.. If not, then your solution works better.
Helper IISmauro 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 :)
Thanks, all! Just tested the solution and it seems to work exactly as I intended! Happy Holidays!
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
