# Calculating avg based on Levels.

Discussion in 'SQL Server 2005 Analysis Services' started by arulbalan, Jul 12, 2007.

1. ### arulbalanNew Member

Hi Mates,

How to calculate Avg based on Levels, But Analysis server is doing normal avg.

But I want Avg on Avg. for example

I have an Employee Hieararchiye by Levels (CTO, PM, PL, TL, TM)

I have daily data of lunch time taken by each employee.

Note:

Lunch Hrs and Day Present are Different Measures.

DayPresent Measure is created by No of Rows Formula

Fact Table Design

Date EmployeeId LunchHrs

LEVEL 5

TM1 = Feb Hrs / Days Present in Feb

TM2 = Feb Hrs / Days Present in Feb

TM3 = Feb Hrs / Days Present in Feb (if this value is =NULL, should not be considered in Level 4)

LEVEL 4

When I calculate Avg Lunch Time taken By TL1 in February

TL1 = Feb Hrs / Days Present in Feb

TL1 = (TL1 + TM1 + TM2) /3 (TM values should be from Level 5)

Note: TM3 not considered here, since value is NULL

LEVEL 3

PL in February =

PL1 = Feb Hrs / Days Present in Feb

PL1 = (PL1 + TL1 + TL2) / 3 (TL values should be from Level 4)

... up to LEVE 1

How to resolve this issue.

I am just a beginner in Analysis service.

2. ### dineshasankaModerator

when you defining the dimension, you need to introduce hierarchies

----------------------------------------
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com

Visit my Blog at
http://dineshasanka.spaces.live.com/
3. ### arulbalanNew Member

hi dineshasanka

this is very very urgent need, please do help me as soon as possible.

even if this is not clear, i am ready to explain in detail....

i have already defined Employee dimension with Hiearhchies. (LEVEL 5, LEVEL 4 ...)

When i calculate last level employee's avg lunch time for a particular month, it is giving result as i expect, bz it is leaf node.

But It is not working for parent nodes as i expect.

fact table structure.
employeeid ##date##lunchtime

where lunchtime is defined as avg measure in aggreatefunction.

if employee x lunch time records for month of jan as follows

jan1 60 mints
jan2 40 mints
jan3 50 mints

we consider he is last level employee, doen't have children employees.
so the avg should be 150/3. i am getting this.

but when i calculate his boss lunch time avg,
1. if boss having lunch time records then boss avg must be calcualted as done for employee x.
2 . then boss avg = (boss avg + sum(sub-ordinate avg values)) / count(sub-ordinate) + 1 (boss).

but analsis service is doing normal avg.

for example

x jan1 60 mints
x jan2 40 mints
x jan3 50 mints
y jan1 60 mints
y jan2 40 mints
boss jan1 10 mints
boss jan2 20 mints

analysis service returns 60 + 40 + 50 + 60 + 40 + 10 + 20 / 7

but i expect value of boss must be
1. xx = (60 + 40 + 50) / 3
2. yy = (60 + 40) / 2
3. bb = 10+ 20 / 2
4. result = xx + yy + bb /3 (here i am computing low level emp avg and moving that to next level computing).