July 19, 2010 at 10:13 am
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
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