Not sure if the title here makes sense. Basically I'm trying to do the following:
I have created a measure for organisation FTE at the start of the reporting period, and another for FTE at the end of the period and created a third measure that takes an average of the two, which is reflective of the standard reporting practice. In a separate dataset I have sickness data within the period, and I want to find out what the sickness per FTE employee is. I can do that for the organisation as a whole easily enough, by creating another measure that divides the sum of days lost to sickness by total FTE, but when I try to drill down to departmental level it divides the sum of days lost to sickness by whole organisational FTE rather than the FTE of that department.
The only way I can see how calculate it accurately by department is to create a set of separate measures to calculate FTE in the period for each department and then divide each departmental sum of days lost by this measure, but that's not ideal and would be visually messy. I would ideally like to set up a measure that would recognise when I am drilling down into sickness by departmental level and would therefore divide the sum of days lost to sickness in a particular department by the FTE value of that department. Does that make sense?
