Forum Discussion

PauloH's avatar
PauloH
Frequent Visitor
8 years ago
Solved

Span of Control

Hi all,

 

I have a table structured like this:

 

EmployeeManager
1 
21
31
41
52
62
72
82
92
103
113
123
133
144
154

 

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:

EmployeeManagerSpan of Control
1 3
213
314
410
520
620
722
870
970
1030
1130
1232
13120
14120
1530

 

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? 

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

11 Replies

  • Smauro's avatar
    Smauro
    Icon for Solution Sage rankSolution Sage
    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

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

      • Smauro's avatar
        Smauro
        Icon for Solution Sage rankSolution Sage
        8 years ago

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

    • tdemoss's avatar
      tdemoss
      New Member
      8 years ago

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

  • 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