MDX; Get aggregation of measure over all values in a hiercharchy except named ones

Question

This tuple gets me an aggregation of measure X in the dimension of [MyHierarchy].[MyHierarchy].&[some_value].

([Measures].[X], [MyHierarchy].[MyHierarchy].&[some_value]) 

But what is the complement - ie Measures.X in the same hierarchy but excluding [MyHierarchy].[MyHierarchy].&[some_value]

I'm sure it's the EXCEPT function but I can't seem to get the syntax right.

Answer 1

The general idea to aggregate over any kind of set is something like:

with member [Measures].[Y] as 
       aggregate( 
         except( [hierarchy].members, { excluded } ), 
         [Measures].[X] )
       )

Aggregate() is handling whatever actual aggregation of the [Measures]. The issue is that if [hierarchy].members contains some intermediate (parent) members, you'll have values counted twice.

If the aggregation is SUM and you've an all member, then the following is giving the expected result :

with member [Measures].[Y] as 
       ( [hierarchy].[all], [Measures].[X] ) 
       - sum( { excluded }, [Measures].[X] )

If no all then you can use members of the first level:

with member [Measures].[Y] as 
       sum( [hierarchy].levels(0), [Measures].[X] ) 
       - sum( { excluded }, [Measures].[X] )

_