The MDX Rank Function

If you read my blog regularly, you may have seen this post from last week where a reader asked how to create  set to get the top 5 members while lumping every member outside of the top 5 into an “Others” member. As a follow up question, another reader asked how we can rank the members being displayed.

We can create a numeric ranking of the Customers by leveraging the MDX Rank function. The Rank function returns a one-based rank of the supplied member of the supplied set. So how do we update our original query to accomplish this?

Here is our original query:

with dynamic set [Top 5 Customers] as 

    Topcount(
        [Customer].[Customer].[Customer].members,
        5,
        [Measures].[Internet Sales Amount]
        )



member [Customer].[Customer].[Others] as 

    Aggregate(
        Except(
            [Customer].[Customer].[Customer].members,
            [Top 5 Customers]
            )
        )

dynamic set [Top 5 & Others] as 

    {
    [Top 5 Customers], 
    [Customer].[Customer].[Others]
    }

Select [Measures].[Internet Sales Amount] on 0,


[Top 5 & Others] on 1


From [Adventure Works]

To create the ranking, we need to create an additional calculated member to display the rank of the customers (which you can see below highlighted in red):

with dynamic set [Top 5 Customers] as 

    Topcount(
        [Customer].[Customer].[Customer].members,
        5,
        [Measures].[Internet Sales Amount]
        )



member [Customer].[Customer].[Others] as 

    Aggregate(
        Except(
            [Customer].[Customer].[Customer].members,
            [Top 5 Customers]
            )
        )

dynamic set [Top 5 & Others] as 

    {
    [Top 5 Customers], 
    [Customer].[Customer].[Others]
    }

member [Measures].[Rank] as

    Rank([Customer].[Customer].CURRENTMEMBER,[Top 5 & Others])

Select {[Measures].[Internet Sales Amount],[Measures].[Rank]} on 0,


[Top 5 & Others] on 1


From [Adventure Works]

 

The Rank function excepts two arguments. The first argument is tuple (or member in this case) that we wish to rank. The second argument would be the set we want to rank. In our case, our set is [Top 5 & Others]. Don’t forget to add your new Rank measure to be displayed on the columns! 🙂

And here’s our results:

image

If you found this helpful, leave me a comment and let me know! And don’t be afraid to post any questions!

One thought on “The MDX Rank Function”

Leave a Reply