Average of children

strippy


i know this question has been around for some time, and have read most of the answers, but well still looking at how to do this in standard edition of sql 2005.

to what i understand Average of children calculates the average not on the lowest level but on the next one (i.e. it sums the values on daily basis but it calculates an average on weekly or monthly basis).

my problem was that i needed to calculate an adjusted price base on the next formula:

i.e.

price hotel 1 - H1
price hotel 2 - H2
price hotel 3 - H3

capacity hotel 1 - C1
capacity hotel 2 - C2
capacity hotel 3 - C3

((H1*C1)+(H2*C2)+(H3*C3))/(C1+C2+C3)

now i used averageofchildren to define measure (price of hotel*capacity of hotel) and measure (capacity of hotel) and created calculated member based on the above formula.
with the use of averageofchildren i get the wanted result. but if i use avg()/count() i get also average of those 3 hotels on the level of day.

now could someone please help me to write a calculated measure or mdx expression that would not use averageofchildren.

i have defined sum measure (price of hotel*capacity of hotel) and sum measure (capacity of hotel).

i have defined dimTime.

is there a way to do this

i hope you all understand my question...

thanx in advance

PedroCGD

Give me the SQL script that generate your tables to understand more exactly what you pretend!

regards!

strippy

hi,

ok i'll try to explain it this way:

i have two measures a_price (a_price is equal price*capacity) and capacity.

lets say my hiearchy looks like this:

1. level Country (highest level)
2. level county
3. level city
4. level hotel

i'm looking at total of 3 months.

now i used calculated member A_PRICE/CAPACITY and defined a_price and capacity as averageofchildren when asked to define aggregation function. everything works fine.

i have 3 hotels in one month, wwhen i look at the level of the city i get avg total of those 3 hotels but not that it summed them and devided them by 3.

so this works in enterprise edition, but what to do in standard edition


ok i defined measures now as sums (standard edition) and i need to calculate

a_price/capacity. i have no special mdx script, i need to define next:

sum on the lowest level, and on the next higher level average function if i use

is it clearer now

PedroCGD

In the datasource view of Analysis Services try to use named calculations to do a_price/capacity!

in your table in datasourceview, right-clik the mouse and add a named calculation!

regards!!

strippy

thanx will look at named calculations. and try it on data to see what i can get. if i get into any trouble will post here.......

PedroCGD

Strippy,

Feel free to post!! If I can I will help you as other persons help me when I need... this is a comunity! :-)

But when your answer is responded, please check as answered!!

regards!!!