I am running into an issue with a calculation. I am calculating FTE’s in order to measure sales per employee. I created a table with rows as branch and use the below measure to calculate FTEs for one division of the company. This measure works great in the table and includes the overhead (main office FTE and temp FTE) in the total row. Below is the measure which works great in a table:
(sorry for the formatting issue - I can’t get the var statements to format correctly)
Medical FTE =
var non_exempt_hrs = CALCULATE([Non-Exempt Hours],DB_OverTime_Trend[Worked in Segment]="DME",FILTER(branch,branch[branch_id3]<>"001"))
var exempt_hrs = CALCULATE([Exempt Hours],DB_OverTime_Trend[Worked in Segment]="DME",filter(branch,branch[branch_id3]<>"001"))
return if(ISFILTERED(branch[branch])
,(non_exempt_hrs/[business hours])+(exempt_hrs/(86.67*2))
,(non_exempt_hrs/[business hours])+(exempt_hrs/(86.67*2))+([Main Office FTE]/2)+(.7*[Temp FTE])
)
The problem that I have with the measure is that when I use it in a stacked line chart there is no context of a branch so it automatically includes the overhead. The report includes RLS for users to only see their branch but they keep asking me why their FTEs are wrong. I ask them to select their branch in a filter and the visuals then reflect only their branch’s FTEs.
I am wondering if there is any way to change the measure so that a user does not have to select their branch from a slicer? This would allow me to pin these visuals to a dashboard and know that the calculations are accurate. Unfortunately I cannot share this report but can take as many screen shots as needed.
This is how I’m thinking about this, hopefully I have it right in my mind.
You need specific calculations completed with the branch is selected versus not selected?
The simple way to fix this is to make it very obvious in the report page or visual to make sure the user selects a branch. Currently I don’t see that in the report and images. This should also be done for any report, make it as intuitive as possible.
It seems like the formula in the FALSE part of the if statement is what needs to adjustment.
Maybe you need to use a iterating formula there it work through each branch within a virtual table, and add the main office FTE to the results.
One thing I’m confused about is why is that FALSE results not producing anything anyway. What’s happening in that formula to produce blank results?
I would also get this in a table, so you can see the actual results that are being returned at all times when auditing the numbers.
Thank you for the reply Sam. It seems easy enough to add text requesting a user to select their branch(s) but unfortunately this does not solve the issue I have with the visual being displayed incorrectly on a dashboard.
I’ve taken your feedback and created a table that allows a user to filter to include overhead or exclude overhead. and changed the FTE calculations to return a switch(true()) statement if the selectedvalue() = “exclude”. This seems to work:
DME Visual FTE =
var non_exempt_hrs = CALCULATE([Non-Exempt Hours],DB_OverTime_Trend[Worked in Segment]="DME",FILTER(branch,branch[branch_id3]<>"001"))
var exempt_hrs = CALCULATE([Exempt Hours],DB_OverTime_Trend[Worked in Segment]="DME",filter(branch,branch[branch_id3]<>"001"))
return SWITCH(TRUE()
,SELECTEDVALUE('FTE Visual Filter'[Overhead])="Exclude Overhead",(non_exempt_hrs/[business hours])+(exempt_hrs/(86.67*2))
,(non_exempt_hrs/[business hours])+(exempt_hrs/(86.67*2))+([Main Office FTE]/2)+(.7*[Temp FTE])
)
I know I didn’t use your solution exactly - I am still trying to get caught up on your videos that I have missed. Thank you for the great content. I am positive I have a lot to learn still.
