MDX - Calculated Member with EXCEPT will not roll-up at all?

1.1k Views Asked by At

I have a Calculated Member were I want to aggregate a measure by YTD. I also want the measure to roll-up on all dimensions. There is one dimension value that I dont want to be part of the aggregation. The problem is when I try to filter this out the aggregation doesnt roll-up on the other dimensions.

This works and returns the correct value for all values in the [EK DIM OBJ KONTO] dimension, and rolls-up fine on all dimensions:

Aggregate
(
  YTD([DATUM D].[Period].CurrentMember)
 ,[Measures].[UTFALL V]
)

When I try to add an EXCEPT-statement to exclude one value in the [EK DIM OBJ KONTO] dimension I get the wrong result. The Calculated Member always returns the same answer, it does not take any notice of the dimensions I am trying to slice it with.

Aggregate
(
    {YTD([DATUM D].[Period].CurrentMember)}
  * 
    {
      Except
      (
        [EK DIM OBJ KONTO].[KTOB2 ID].[KTOB2 ID]
       ,[EK DIM OBJ KONTO].[KTOB2 ID].&[-]
      )
    }
 ,[Measures].[UTFALL V]
)
1

There are 1 best solutions below

0
On BEST ANSWER

This is against the AdvWorks cube but seems to be working:

WITH 
  MEMBER [Measures].SumYtd AS 
    Aggregate
    (
      {YTD()}
     ,[Measures].[Internet Sales Amount]
    ) 
  MEMBER [Measures].SumException AS 
    Aggregate
    (
        {YTD()}
      * 
        {
          Except
          (
            [Customer].[Customer Geography].[Country].MEMBERS
           ,[Customer].[Customer Geography].[Country].&[United States]
          )
        }
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].SumException
   ,[Measures].SumYtd
  } ON 0
 ,[Date].[Calendar].[Month].&[2007]&[11].Children ON 1
FROM [Adventure Works];