MDX, how to change aggregation function inside a hiarachy RRS feed

  • 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
    MEMBER Measures.LevelCount
    DESCENDANTS([Date].[Calendar].CurrentMember,[Date].[Calendar].[Month] ,LEAVES).count *	Measures.DistinctCountx
    SELECT [Date].[Calendar].members on 1
    ,Measures.LevelCount on 0
    FROM [Adventure Works]


    Here the DistinctCountX is your Distinctstore count.
    Friday, July 29, 2011 10:02 AM