Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
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.
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
@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.
@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.
@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 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?
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
79 | |
64 | |
62 | |
56 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |