Forum Discussion

PauloH's avatar
PauloH
Frequent Visitor
8 years ago
Solved

Span of Control

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...
Need Help
  • Smauro's avatar
    Smauro
    8 years ago

    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

wwhittenton's avatar
wwhittenton
Icon for Helper II rankHelper II
8 years ago

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