Calculate KPI on dimension hierarchy node level

[Former Member]

Hi,

we have a KPI that is a simple multiplication of an amount reported on one account with a percentage (amount) reported on another account. For individual entities the calculation works fine.

	entity1	entity2	entity3	group
percentage	10%	15%	10%	?1?
amount	100	90	80	270
KPI	10	13,5	8	?2?

The problem we have are the fields ?1? and ?2?.

At the moment BPC says ?1? is 35% as the simple sum of the entities. What we would require instead would be the average of the amounts reported by the entities.

The KPI would need to be calculated on the basis of this average (10+15+10)/3=11.6 multiplied with the total amount of 270, giving us 31,32.

How can we achieve this?

Thanks,

Arnold

[Former Member · Accepted solution]

As for ?1?, have a look at :

https://scn.sap.com/thread/3583139

You probably have to :

1) Create a PERCENTAGE_COUNT with formula = IIF(PERCENTAGE=0, NULL, 1)

2) Create a PERCENTAGE_CALC with formula = IIF([ENTITY].[H1].currentmember.name = "Group",PERCENTAGE / PERCENTAGE_COUNT, PERCENTAGE),SOLVER_ORDER = 9

This will allow to generate a 1 for each member where percentage exists and then when on the Group member of the Entity dimension, to have the total calculated as an average of all percentage values

Then your KPI formula will have to be = Percentage_CALC * Amount, Solver_order = 10

[jrg_finster3]

Hi Arnold,

in my opinion ?2? is a typical "before aggregation" calculation (you need to calculate basemembers and parent should be sum of childs and not sum(amount)*sum(percentage).

for me "before aggregation" => always script logic

for ?1? i would follow Vadims Doc (but attention some formulas in the post have some minor errors)

http://scn.sap.com/docs/DOC-36399

Regards

Jörg

[Former Member]

You do not necessarily need script logic to manage agregation and calculation orders, SOLVE_ORDER in formula does the job very nicely ^^

[jrg_finster3]

yes you are right

[Former Member]

If you have ?1? solved, the ?2? shoudl be easy :

Add a formula in KPI = 'percentage * amount, SOLVE_ORDER=10

The solve order will force the calculation formula to be performed at the latest priority, after the agregation are done on the dimension and should result in performing for Group :

?1? x 270

You can first test this step and check it works accordingly, so for now the result would be for "Group" :

35% x 270 = 94,5