Hi all,

Does anyone know how I can achieve this using AS2000?

Example

Measure1 M2 M3 M4 M5
1000 100 10 50 60
950 50 10 50 60
500 100 10 50 80
600 100 50 50 60
700 100 10 50 0
500 200 10 50 60

Imagine I have a time dimension in play... how can i have Measure1 NOT
aggregate up but the other measures DO?

Current result when aggregated up to top level
Measure1 = 4250

Desired result when aggregated up to top level
Measure1 = either AVG or SUM/RowCount - But when I drilldown, I am able to
see the actual figures by day.

Hope this is clear?

Regards
Immy

does Measure1 is a calculated measure?


"Immy" <therealasianbabe (AT) hotmail (DOT) com> wrote

No. They are all normal.

"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote

ok.

my recommendation:
rename this measure to measurehidden1 and set the visiblity to false
create a new calculated measure called "Measure1"
setup the formula like:
iif(dimension.currentmember is dimension.[all level],
avg(dimension.children, measures.measurehidden1), measures.measurehidden1)

if you are at the all level of the dimension (where you don't want the sum
but the avg) then do an avg of the children of this member, else you want
the normal value.

but, if you need to ALLWAYS calculate the avg value for any position in the
cube, then do this: (and not only at the all level or not only against 1
dimension)
rename this measure to measurehidden1 and set the visiblity to false
create a new measure which is a count like measurehiddencount and set the
visiblity to false
create a new calculated measure called "Measure1"
setup the formula like:
iif(measures.measurehidden1 = 0 , null, measures.measurehidden1 /
measures.measurehiddencount )

this always evaluate the avg value

you can search on the web for other way to do this.
like avg over time formulas etc...


"Immy" <therealasianbabe (AT) hotmail (DOT) com> wrote

Assuming that you're using AS 2000 Enterprise Edition, here's a sample
Foodmart query with a calculated cell which returns average [Unit Sales]
at the year level, but the sum of [Unit Sales] at lower (quarter or
month) levels:

for '({[Measures].[Unit Sales]}, [Time].[Year].Members)'
as 'CalculationPassValue([Measures].[Unit Sales],
-1, RELATIVE) / [Measures].[Sales Count]',
FORMAT_STRING = '#,#.00'

select {[Measures].[Sales Count],
[Measures].[Unit Sales]} on 0,
Non Empty DrillDownLevel([Time].[Year].Members) on 1
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***