1 of 1 people found this helpful
Could you utilize a parameter (Select Year, Quarter, Month/4weeks, or Week) to dictate what to divide by?
i.e., set up a calculated field as
IF [Parameter] = 'Year' THEN 40*52
ELSEIF [Parameter] = 'Quarter' THEN 40*13
ELSEIF [Parameter] = 'Month' THEN 40*4
ELSEIF [Parameter] = 'Month' THEN 40
Then use your calculated field in your FTE calculation?
You could also use your parameter to toggle between sheets a la this KB article:
Thanks for your response Mark!
I actually started going down that direction and it wasn't panning out (particularly has 2 months every year have 3 pay periods).
However, I found a way that worked:
( sum ( [Total Paid Hours] ) / CountD ( [PayPeriod] ) ) / 80
I discovered that working from the bottom up was much more affective than trying to work from the top down.
By dividing the total number of hours by the number of pay periods, when I drill down, the equation remains consistent because both hours and pay periods change proportionally.
I had to include "Sum" before [Total Paid Hours] to get the Grand Total to work correctly.