Span of Control Calculation

[mihirjoshi]

Hi Everyone, 

I'm trying to build a table in PowerBI that calculates Span of Control (Dashboard attached)

Formula  = Total Employees at NEXT Level / Managers at THAT level

E.g. For Level 5 (Sr. Mgr) the answer should be: 31/11 = 2.81

       For Level 6 (Mgr) the answer should be    : 41/12 = 3.41

The input data has 179 rows/ employees that are pivoted /summarized into 8 levels in this PowerBI table visual.

Org Level is a field in the input data that is being used to summarize the 179 rows/ employees.

Adding a new column to the main data source doesn't seem to help since it would be summarized in the visual and then dynamically filtered/ sliced. 

I'm thinking It would have to be a dynamic formula based column in the table above. 

I have attached the dashboard and input file along with this.

@Smauro @Greg_Deckler I saw you solved a similar topic so pls guide me if you know how to get this in PowerBI.

I have left some of my unsuccessful attempts in the dashboard attached.

Any help would be greatly appreciated.

Thanking you in advance!

Mihir

[v-yalanwu-msft · Accepted solution]

Hi, @mihirjoshi ;

You could change "ALL" to "ALLSELECTD".

Measure 2 = 
var _nextlevel=MAX('Table'[Org Level])+1
return 
CALCULATE(SUM('Table'[EmployeeID]),FILTER(ALLSELECTED('Table'),[Org Level]=_nextlevel))/SUM('Table'[Managers])

Or

Measure = 
var _nextlevel=MAX('Table'[Org Level])+1
return 
CALCULATE(COUNT('Table'[EmployeeID]),FILTER(ALLSELECTED('Table'),[Org Level]=_nextlevel))/COUNT('Table'[Managers])

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @mihirjoshi ;

Try it.

Measure = 
var _nextlevel=MAX('Table'[Org Level])+1
return 
CALCULATE(COUNT('Table'[EmployeeID]),FILTER(ALL('Table'),[Org Level]=_nextlevel))/COUNT('Table'[Managers])

Or

Measure 2 = 
var _nextlevel=MAX('Table'[Org Level])+1
return 
CALCULATE(SUM('Table'[EmployeeID]),FILTER(ALL('Table'),[Org Level]=_nextlevel))/SUM('Table'[Managers])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mihirjoshi]

Hi,

Thank you so much for your help.

Initially, it worked perfectly for the sample data set I shared.

However, when I filter/slice my dashboard, it doesn't work accurately.

I think this is because the table I shared is a summarisation of the raw base data based on 'Org Level' field.

My raw data looks like this (It's a database of 179 employees that I summarize based on Org level in PowerBI)

Request your help to modify the measure to make it dynamic in a summarised table visual/view in BI

Thank you for all your help so far.

Best,

Mihir

[Applicable88]

@mihirjoshi where did you make the summarisation? If you did it via PowerQuery, I don't think the summarize is a problem, because the data was already loaded in that form and you just doing a new measure base on that view of data.

Please try the following:

Leave the measure I showed you how it is,  and create a new measure.

In the new measure you write:

Measure2 = sumx ('Table' , [Measure])

'Table' is just a placeholder in whatever folder your Measure is located. Please adjust accordingly.

That should solve your problem. 

[Applicable88]

@mihirjoshi sorry for the late response. I just create a sample file to reproduce what you want. I found my mistake. I just mixxed up the divider. 

I now got the result you want:

Meas =
VAR _NextLevel =
    MAX ( Tabelle1[Org Level ] ) + 1
RETURN
    CALCULATE (
        MAX ( Tabelle1[Workforce Total] ),
        FILTER (
            ALL ( Tabelle1 ),
            Tabelle1[Org Level ] = _NextLevel
        )
    )
        / CALCULATE (
            MAX ( Tabelle1[Managers] )
        )

Hope this will help you. 

Best. 

[Applicable88]

@mihirjoshi I think a calculated column is not necessary. Can you Try this measure instead? If there are small changes, please adapt to your table: 
Measure = 
var _NextLevel = Max(OrgLevel) +1

return

calculate (Max(Managers) / Calculate (Max(WorkforceTotal), Filter(All( EmployeeTable), OrgLevel = _NextLevel)

[mihirjoshi]

@Applicable88  

Thanks for your help. i put the measure you suggested with a couple alterations for my table, but the result isn't correct.

for eg: Org level 7 -> Measure should be 72/20=3.6

[Applicable88]

@mihirjoshi why you actually count EmployeeID? you want the max value of that row to divide with the other value and not the count of it. What happens if you put MAX instead of COUNTA?

[mihirjoshi]

i did what you said, and i think the problem here is that everything is getting filtered for the "next level" as opposed to just the workforce count, i.e. the employee ID column
can you help me modify this formula to take the numerator, i.e. the employee ID from the next level, and "manager Y?" from the same level?