Set analysis conflict

[#1]

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

Mel

[rubenmarin]

Hi Mel, your expression can work like this?:

=Sum({<DemandDriver={'Headcount'}>}[HeadcountSum])*
pow(((($(vNoHRGrowthRate)-1)*([cHeadcount])), YearIndex)

I separated the first Sum, so each operation has his own set analysis.

[#3]

Hi Ruben,

This is Tom, I'm working with Mel.

I'm afraid that doesn't work as ([cHeadcount]) is a field value that changes depending on which [HeadcountSum] is being used.

In other words, if I close off the first part of the formula as you show, then it doesn't know which [cHeadcount] value to use and returns a null.

Thanks,

Tom

[#4]

Hi Ruben,

I'm afraid that doesn't work as ([cHeadcount]) is a field value that changes depending on which [HeadcountSum] is being used.

In other words, if I close off the first part of the formula as you show, then it doesn't know which [cHeadcount] value to use and returns a null.

Thanks,

Mel

[rubenmarin]

And using Aggr to calculate each [HeadcountSum] value?:

=Sum(Aggr(Sum({<DemandDriver={'Headcount'}>}[HeadcountSum])*
pow(((($(vNoHRGrowthRate)-1)*([cHeadcount])), YearIndex), [HeadcountSum]))

A sample document will be nice to make tests.

[#6]

Thanks so much for your effort Ruben, I've tried that as well but I'm afraid I still receive a null value.

I'm afraid I can't really distribute this either - hands tied.

As a bit of extra information:

- If i calculate the value of vNoHRGrowthRate separately and then plug in that value (it's approx 0.99) then I receive the right answers.

It seems all I need to do is to calculate that value separately and then plug it in.

Any ideas?

Thanks again for all your help.

Mel

[rubenmarin · Accepted solution]

Hi, to give another guess with this last option, you can start the variable with an equal sign, then it should return the result of the variable,ie change variable value from:

Sum({....})

to:

=Sum({....})

[#8]

Yes!!

That's done it.

Thanks Ruben - I knew deep down it would be something simple like that. Thank you for your help.

I'll mark that as correct.

Mel