Post reply

Calculated measure

  • July 19, 2010 at 10:13 am

    #144185

    Hi Everyone.. i am new to Analysis services and need a help in writing a MDX.I have measure called Impressions and now i need to calculate the rank of that ie Rank= Rank*Impressions/Sum(Impressions) of a slice so i need a approach of how to proceed with that.. Any Help would be Appreicated..Thanks

  • July 21, 2010 at 12:05 pm

    #1196374

    As a general form for Percent of Set queries, you could use a calculated measure with this syntax

    with member [measures].[percent_rank] as ([Measures].[Impressions],[aDimension].[aHierarchy].CurrentMember) / ([Measures].[Impressions],[aDimension].[aHierarchy].CurrentMember.Parent)

    The first term gets you the portion of the whole, for a given Dimesion member ([Measures].[Impressions],[aDimension].[aHierarchy].CurrentMember)

    The second is the total for the population or whole to which the current dimension member for aDimension belongs: [Measures].[Impressions],[aDimension].[aHierarchy].CurrentMember.Parent)

    If I understand, you want to multiply by this the RANK, correct?

    Then you would create a set, ordering by the Percent_Rank measure, and use the RANK() function with aDimension and aHierarchy to assign a ranking number to each aDimension member.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply