Aggregation advice

Hi, I am having some trouble with changing the default aggregation behavior of my cube. I need to be able to change the aggregation at different levels of a dimension.

I am currently exploring the possibility of using custom member formulas, placing MDX script into a column within my dimension meaning that I could define a custom roll up at each level in my hierarchy – is this the best way?

I have two Dimensions as part of a pivot table:
DIMTime = [Year].[Quarter].[Hour]
DIMSpatial = [Group_1].[Group_0].[Route].[Link]
And a calculated measure = SUM(x)/SUM(N)

When I ‘roll up’ (total) this is what I am currently getting:

DIM Time

Group_0 2006

1 0.9398

2 0.9471

Total 0.9424

I want to change the roll up to be 0.9398 + 0.9471 = 1.8869

Could anyone give me some guidance on how the MDX Script should look? I have been trying lots of combinations but keep getting a #Value! Error. I haven’t yet been able to implement a custom aggregation so if I am going down the wrong avenue or if you think there is a better way of doing this please let me know…

Thanks in advance for any advice you can give me.

Simon

If you want the roll up to occur from fact table granularity, you could define a Named Calculation like 'x/N', then define a "sum" measure on it (assuming that the 2 measures are in the same fact table/measure group). If they are in different fact tables/measure groups, you might be able to use a measure expression|||

Thanks for your post Deepak and sorry for the slow response.

You are right in assuming that the 2 measures are in the same fact table. In trying to explain the problem I have over simplified my example in the first post. I have tried to put together this illustration

to explain further:

[On the left is what I am trying to achieve and on the right is what I am getting right now]

Click Here

I see from your description above that the default ‘SUM’ aggregation will work. But if I now wanted to ‘/n’ at each level in the hierarchy how would I go about changing this behaviour?

edit: Adding link to diagram instead