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.
Image without the branch selected in a slicer:
Image with the branch selected in a slicer:
Any help is appreciated!