Hi all,
I am trying to predict the total headcount of a Business over the next few years. To do this we've segmented the workforce and said that some people are going to be affected by Revenue Growth while others will be affected by Digital Revenue.
As part of this we are also assuming that places like HR will be affected by the headcount in the rest of the business (not including themselves).
To calculate the Headcount changes in HR we have to calculate the headcount Growth Rate in the rest of the Business. This has left me with a variable where I sum everything where {<[DemandDriver] - = {'Headcount'}>}. This variable works on it's own and gives me the correct value. The variable is called vNoHRGrowthRate
So to calculate the headcount Growth in HR I end up with this formula:
=Sum({<DemandDriver={'Headcount'}>}[HeadcountSum]*
pow(((($(vNoHRGrowthRate)-1)*([cHeadcount])), YearIndex))
Where [cHeadcount] is a field where the is value dependent on the particular [HeadcountSum] is being Summed.
[YearIndex] is a coded inline table where 2015=0, 2016=1 etc.
The problem is that the set analysis in Red is clashing with the set analysis in the vNoHRGrowthRate variable and is returning a null value. (The section in Red says Sum everything where it IS equal to headcount and in the variable it additionally tells it to sum everything where DemandDriver is NOT equal to headcount)
If I replace the variable with its numerical value then the formula works - i can't leave it like this though as the values change dynamically.
I just need to put in some notation that tells it to ignore the previous set analysis before calculating the value of vNoHRGrowthRate.
Is there a way to do this?
Thanks and kind regards
