Calculate average in dimension formula

[Former Member]

Hi Experts,

I need to calculate the average salary of all employees within a function (Marketing for example). Do you know if this is possible via dimension formula?

I have tried the following formula on account SALARY_AVG but it does not return any result ...

AVG([ACCOUNT].[SALARY],[EMPLOYEE].[TOT_EMPLOYEE].CHILDREN,[COST_CENTER].[TOT_CC].CHILDREN)

BPC version: 10.1 SP3 on Hana

Employee	Function	Cost_center	Account	Signeddata
EMP001	MARKETING	CC01	SALARY	1000
EMP002	MARKETING	CC02	SALARY	1200
TOT_EMPLOYEE	MARKETING	TOT_CC	SALARY_AVG	1100

Thanks in advance,

Camille

[Former Member]

Hi Camille,

Try using member grouping in the dimension then use the AVE formula in that local member.

Andy

[former_member186338]

Hi Camille,

Please read my document:

Also - what do you mean by Function? Dimension?

Vadim

P.S. And please, provide more details about calculation algorithm

[Former Member]

Hi Vadim,

Thanks for replying. By function I mean "job", "position". Yes this will be a dimension in my HR model. Each employees can potentially change position.

Users will plan, by entity (group of CC), the personnal costs. They want to see the average salary by position.

I need to add all salaries of function "Marketing", and divide it by the number of employees within this function.

I guess I could use the number of ETP which will be sent in the database with the salaries ...

@ Andy, I will not display all employees in my report so I am not sure about this option...

[former_member186338]

Hi Camille,

"Yes this will be a dimension in my HR model" - means that you don't have full dimensions list now??? But how to work with formulas?

Please describe all dimensions to be used in the model...

And "Users will plan, by entity (group of CC), the personnal costs." - what do you mean?

Vadim

[Former Member]

We are still in a general design phase. I'm trying to explore faisibility. Let's just assume I only have the 4 dimensions as shown in first mail (I will have on top category, time, datasource, and few others).

But anyway the formula I was thinking of will not be correct as it will count my employee as 1 FTE.

Formula should be SALARY_AVG=SALARY/ETP

[former_member186338]

If you have FUNCTION as a parent in Employee dimension - it's one approach, if it's a separate dimension - another...

Vadim

[Former Member]

Yes you're right. Function could be a hierarchy of employees. We need to discuss this in our next workshop.

If a separate dimension, I will have the number of ETP by function, formula can then be [SALARY]/[ETP].

If a hierachy, formula will be [SALARY]/([ETP],[EMPLOYEE].currentmember.parent)

... or something like this?

[former_member186338]

Hi Camille,

You can get count of employees like:

If you have account ETP filled by 1 for each employee ID (and dummy other dimensions) you can use tuple with this account in average calculations.

Also you can use COUNT in hierarchy with something like:

[SOMEACCOUNT]/COUNT(DESCENDANTS([SOMEDIM].CURRENTMEMBER.PARENT, 20, LEAVES),INCLUDEEMPTY)

Vadim