# MDX, how to change aggregation function inside a hiarachy

• ### Question

• We have a measure of [Distinct Store Count], suppose we have 2 stores, and every day both stores are visited, so the month level [Distinct Store Count] is still 2. And the year level [Distinct Store Count] is also 2. But the requirement is that, for level above monthly level, we need to sum up [Distinct Store Count] for each month, so the year level [Distinct Store Count] should be 2*12=24. and quarter level, it should be 2*3=6.

In another word, for month level, we need distinct count aggregation, but above month level, we need to aggregation function of sum. How that could be done. I'd like to use something like be following, but can't get the exact expression.

(Calendar.year,[Distinct Store Count])=sum(Calendar.month,[Distinct Store Count])

(Calendar.quarter,[Distinct Store Count])=sum(Calendar.month,[Distinct Store Count])

Friday, July 29, 2011 1:05 AM

• Try creating a calculated member as shown in the below Adventure works sample

```WITH MEMBER Measures.DistinctCountx

as

'2'

MEMBER Measures.LevelCount

AS

DESCENDANTS([Date].[Calendar].CurrentMember,[Date].[Calendar].[Month] ,LEAVES).count *	Measures.DistinctCountx

SELECT [Date].[Calendar].members on 1

,Measures.LevelCount on 0