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]
)
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];