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:

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