Operations on dimension members

[Former Member]

Hi Experts!!

I need ideas to solve this requirement that is a bit strange;

Initially I have a KPI and a dimension, which it crosses by.

When I drill down on the TOTAL PROCESO, I can check the values for the members of the dimension;

What I need; assign weight for each member (example: Gobierno Corporativo 60%, Planeación Estratégica 40%)

And calculate a formula for the TARDEV like this;

(A * 60%) +  (B * 40%)

any idea how to do this??

thanks a lot!!

[Former Member · Accepted solution]

There are two parts to this answer.

Firstly what you ask can be done. It is a bit complex and I will not provide the solution at this point.

The second part is that what you ask may not be what you really need and so I will address this. The most common time people want to weight over dimension members is when the actual is really a ratio or percentage. e.g. tonnes per worker. Imagine A is a big unit producing 60 tonnes and B is a small unit producing 40 tonnes. You want actuals to be tonnes per worker. A has 2 tomes per worker and B has 3 tonnes per worker. The parent cannot be 5 tones per worker which is what would usually happen when you consolidate. Nor can the parent be 2.5 tonnes per worker (average of A and B).

The tonnes per worker actually comprises two measures, tonnes and workers. A has 60 tonnes, 30 workers and a score of 2. B has 40 tonnes, 13.33 workers and a score of 3. The parent has 100 tonnes, 43.33 workers and a score of 2.308.

Is this is what you want to achieve? If so assume you have a KPI TonnesPerWorker.

Add four new measures TonnesAct, TonnesTar, WorkersAct and WorkersTar. These will never be displayed in your scorecard. Load data to these measures at the base level and consolidate.

Now change TonnesPerWorkerAct to a virtual measure as TonnesAct / WorkersAct.

Now change TonnesPerWorkerTar to a virtual measure as TonnesTar / WorkersTar

Change TonnesPerWorkerTarDev to (TonnesAct / WorkersAct) % (TonnesTar / WorkersTar).

Finally if this is what you want to achieve there is a further refinement tat allows you to load the target as a ratio (TonnesPerWorker) and actuals as the two base measures (tonnes and actuals). I can expand on that but first let's check whether I have correctly understood your requirement.

Cliff

[Former Member · Accepted solution]

Hi Clifford,

this is exactly what I need, your answer is very helpful to me, now I got to socialize it with the client, to know her opinion.

Thanks!!

[Former Member · Accepted solution]

Hi Clifford,

I have already talked about the answer you gave me, and they considered it, but they also want to know the complex way you mentioned at the begining so they can take a decision.

Is it possible that you can explan me how could it be this second option??

Thanks!!

[Former Member · Accepted solution]

So now we’re on the right track with actuals we need to look at targets. Typically the users do not think in terms on Tonnes and Workers. Rather they think of TonnesPerWorker. For actuals we load Tonnes and Workers from the data source but hide them from the end user. For Target this is not so easy as a human is expected to set the target.

Importantly in some cases the number of Workers is not under control of the business and so they do not want to set a target for this. Their view is they can control productivity and so should be measured on TonnersPerWorker. They can’t control the number of available workers and so should not enter target for this.

There are three methods.

Method 1

Each member of the hierarchy has a different target. There is no relationship between the parent target and the child target. TonnesPerWorkerTar is entered though EA at each level of the hierarchy and never consolidated.

Score is defined as (TonnesAct / WorkersAct) % TonnesPerWorkerTar. There is no need to have TonnesTar or WorkersTar.

Method 2

A single target is set for the entire organisation. TonnesPerWorkerTar is entered though EA at the top level of the hierarchy and then use a procedure to copy it to all other members.

Score is defined as (TonnesAct / WorkersAct) % TonnesPerWorkerTar. There is no need to have TonnesTar or WorkersTar.

Method 3

Targets vary across the hierarchy. Site B has better equipment and so is expected to be more productive. The sites have no control over the number of workers. Total target has to be weighted by the site targets. TonnesPerWorkerTar is entered though EA at the base level of the hierarchy and never consolidated.

When the actuals come in we run a procedure to calculate the TonnesTar at the base level of the hierarchy. The calculation is TonnesTar = TonnesPerWorkerTar  * WorkersAct. (Not a misprint. We are using actuals.)

Then we consolidate TonnesTar and WorkersAct to the top of the hierarchy.

Finally we use a procedure to calculate TonnesPerWorkerTar at the top of the hierarchy. The formula is TonnesPerWorkerTar = TonnesTar / WorkersAct.

This has two advantages. It provides a true weighted target and does not require the planners to plan WorkersTar, something outside their control. The one disadvantage is we don’t know the consolidated target until the actuals arrive.