I'm trying to calculate employee turnover where the numerator is annualized only for the current incomplete year.

The closest I have gotten is TER/HC as a calculated Field in a pivot table. This allows me the flexibility to drag and drop fields, pivot, etc and have this field calculate turnover no matter the slice. But the problem is if I am viewing this year over year and say for example we are three months into the current year, current year will appear artificially low because it is not an annual number. The solution is to annualize current year.

I thought I could use an if function in a calculated field like so: if(FYR=2014, TER/2*12,TER)/HC. But this didn't work instead I got the same result as TER/HC. In this case FYR is Fiscal Year and we assume that so far there are two months in current fiscal year. TER is number of people who terminated (left the company) and HC is headcount.

What am I doing wrong? Can an if function be used in a calculated field? Do I have to use PowerPivot instead?

EDIT

Now that I figured out the first problem I have a couple new related problems that I need to solve to get to my complete solution:

A) Why am I getting a #NUM! error in my grand total whenever I filter on FYR (Fiscal Year - a numeric column). This is my measure formula: CALCULATE(if(Max(AttritionExample[FYR])=2014,sum(AttritionExample[TER])*12,sum(AttritionExample[TER]))/sum(AttritionExample[HC])).

TER and HC are numeric columns

B) I added a new column to my Excel file then clicked PowerPivot window. I see my field was added there. But when I go to click update all and refresh my power pivot table the new field does not show up right away, not until a long while after.

up vote 0 down vote accepted

I figured out how to do this using PowerPivot and by creating a new measure which is =if(Max(AttritionExample[FYR])=2014,sum(AttritionExample[TER])*12,sum(AttritionExample[TER]))/sum(AttritionExample[HC]). AttritionExample in this case is the name of my data set. Defnition of rest of columns is given in the original question. I still have to figure out how to make the hard-coded values of the formula update each time period but for now this works.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.