I am trying to create a cube where one of the measures is "summed" across 2
of the six available dimensions, BUT is "averaged" accross the other 4
dimensions...

Can anyone suggest a formula or strategy to create this measure??

I am having trouble visualizing what you are trying to do, but at a
guess I would say that you would need to create 2 measures

Following the fact that an average is a sum divided by a count.

1) counting the crossjoin of the descendants (probably down to the
leaves) of the 4 dimensions you want to average. This one can be set to
visible = false.

2) divide the measure by the count you created in step 1.

If this is not right you might need to post some data to illustrate your
problem. Unless anyone else has any ideas.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <506E0704-5812-438D-8336-6825EDEA320A (AT) microsoft (DOT) com>,
kelyb (AT) discussions (DOT) microsoft.com says...

you could use custom member formula for some of the dimensions

kelyb wrote:

--
Message posted via http://www.sqlmonster.com

I think I managed to work it out (with some help)... I have a few dimensions
along which aggregations are fine, however, along the 4 different time
dimensions, I would need to average the measure in question... here is the
formula for the calculated member I created (note: a CASE statement in SQL
2005 would do the same thing):

Iif([Date Dim Type 1].currentmember.level.ordinal > 0, Avg( Descendants(
[Date Dim Type 1].CurrentMember, [Date Dim Type 1].[Date Name] ),
[Measures].[Special Measure] ),
Iif([Date Dim Type 2].currentmember.level.ordinal > 0, Avg( Descendants(
[Date Dim Type 2].CurrentMember, [Date Dim Type 2].[Date Name] ),
[Measures].[Special Measure] ),
Iif([Date Dim Type 3].currentmember.level.ordinal > 0, Avg( Descendants(
[Date Dim Type 3].CurrentMember, [Date Dim Type 3].[Date Name] ),
[Measures].[Special Measure] ),
Iif([Date Dim Type 4].currentmember.level.ordinal > 0, Avg( Descendants(
[Date Dim Type 4].CurrentMember, [Date Dim Type 4].[Date Name] ),
[Measures].[Special Measure] ), 0))))



"kelyb" wrote: