This site uses cookies to deliver our services and to show you relevant ads and job listings.
By using our site, you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service.
Your use of Stack Overflow’s Products and Services, including the Stack Overflow Network, is subject to these policies and terms.

Join us in building a kind, collaborative learning community via our updated
Code of Conduct.

Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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.

1 Answer
1

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.