Prologika Forums

I've been trying to apply what I've learned about the EXCEPT function but I'm not quite getting the correct answer back from my MDX.

I have a dimension with four values ... lets call the ONE, TWO, THREE and FOUR.

I have a calculated member for average

...

I want to compare the average associated with the dimension member ONE ... versus the average associated to the dimension members TWO, THREE and FOUR.  So in SSRS I created a calculated member called "OneAvg" as such:  ([DimensionName].[ABC].&[ONE], [Measures].[Average]) ... which works.

What I'm not sure about is how to create the calculated member "NonOneAvg" that simply gets the average over the TWO, THREE and FOUR members.

Any ideas?

Thanks 

To my understanding you cannot use Aggregate over calculated members. Instead, you need to repeat your Average expression in the new calculated member, e.g.;

 Aggregate (

                 EXCEPT (Dimension.[ABC].[ABC].Members, [DimensionName].[ABC].&[ONE]),

                  [Measures[.[Sales Amount]

                )

/

[Measures].SomeCount