Calculating average by hierarchy level

Hi all,

I have a problem which needs to be sorted out immediate in Analysis service Cube. My requirement is as follows

The following data explains the average value of each employee in corresponding level.

Level1 - > E1 – (25hrs /25days) =1 hrs/day

Level2 - >E2 – (125hrs /25days) = 5 hrs/day

Level3 >E4 – (150hrs /25days) = 6hrs/day

Level4 > E6 – (100hrs /25days) = 4hrs/day

Level4 > E7 – (75hrs/25days) = 3hrs/day

Level4 > E8 – (175hrs/25days) = 7hrs/day

Level3 >E5 – (75hrs/25days) = 3hrs/day

Level2 - >E3 – (100hrs /25days) = 4hrs/day

Eg:

I have productivity records of each day and each employee. I need to calculate avg of each last level employee productivity by monthly. Again Last level employee productivity avg must be added up with their immediate head. But, when I define a Measure Item as avg in the cube, it sums all the values of lost level employees & headand divides with number of records (normal avg).

My requirement is calculating each head avg by sum of each last level employee avgs / no of employees. If head having value, he too will be added. Again Head’s Avg will be added up immediate head.

The following calculation gives average value at each level.

Average of Level 3(E4) = > (4+3+7+6)/4 = 5 hrs/day< = (E6+E7+E8+E4)/4

Average of Level 2(E2) = > (5+3+5)/3= 4.333< =avg(Level3(E4))+avg(Level3(E5)))/2

Average of Level 1(E1) = > (4.333+4+1)/3 = 3.111

< = avg(Level3(E2))+avg(Level3(E3)))/2

Formula for average of level :

: (Sum of Children value + Head Value of Corresponding children) / (No.of Children +1)

I want to calculate average of each employee as well as average of each level in cube (SQL Server Analysis Services).

Thanks in advance

Thiru

I'm not 100% clear on what exactly you are wanting to calculate, so I started with the bolded string towards the end of your post:

(Sum of Children value + Head Value of Corresponding children) / (No.of Children +1)

Looking at this, I worked through some calculations on Adventure Works. Hopefully there is something in there that helps lead you to your answer.

Take a very close look at the formulas and the data returned by each. There is a very important concept illustrated in this example regarding Data Members. In a parent-child hierarchy, a member may have data of their own as well as data from its children. These are stored separately. Books Online has a good article on this, "Working with Attributes in Parent-Child Hierarchies ", which explains this in better detail than I can here.

Hope this leads you to a solution,
Bryan

Code Snippet

with member [Measures].[Num of Children Inc Self] as

COUNT(DESCENDANTS([Employee].[Employees].CurrentMember,,SELF_AND_AFTER))

member [Measures].[Self Quota] as

([Employee].[Employees].DataMember,[Measures].[Sales Amount Quota]),

format="Currency"

member [Measures].[Children Quota] as

SUM(

DESCENDANTS([Employee].[Employees].CurrentMember,,AFTER),

([Employee].[Employees].DataMember,[Measures].[Sales Amount Quota])

),

format="Currency"

member [Measures].[Self + Children Quota] as

[Measures].[Self Quota]+[Measures].[Children Quota]

member [Measures].[Avg Quota] as

[Measures].[Self + Children Quota]/[Measures].[Num of Children Inc Self]

select

{

[Measures].[Num Of Children Inc Self],

[Measures].[Sales Amount Quota],

[Measures].[Self Quota],

[Measures].[Children Quota],

[Measures].[Self + Children Quota],

[Measures].[Avg Quota]

} on 0,

DESCENDANTS([Employee].[Employees].[Brian S. Welcker],,SELF_AND_AFTER) on 1

from [Adventure Works]

;