Calculating Percentile in SQL Server Analysis Services MDX

In many organizations like the financial and health sector, knowing a average (mean) number isn’t enough. They also want to know a ranking of the salespeople, patients or products with their percentile. In other words, how does your Germany branch stack up to its peer branches? The result, I’d be after is something like the following in Analysis Services:

The code that is covered here has been broken into many steps that could easily be done in a single few steps. I’m doing this to show the code incrementally and could easily be altered to your liking. To let’s begin!

First, we must create an ordered set that we can later use to rank the dataset on. I’m ordering the data based on the sales amount where my tuple is based on Subcategory. I’m also filtering the data to where we have sales. This step is optional but will come into play in this example later.

WITH SET [SalesRankSet] AS
FILTER(ORDER(
[Product].[Subcategory].CHILDREN ,
   [Measures].[Internet Sales Amount],BDESC), [Measures].[Internet Sales Amount] > 0)

The next step is create a rank measure which ranks our set that we just created from 1 to N. This is done through the RANK function while passing in the set shown below:

MEMBER [Measures].[SalesRank] AS 
     RANK([Product].[SubCategory].CurrentMember, 
                 [SalesRankSet])

The next measure is the count of how many subcategories you have. You’ll use this later in the formula. One step that bit me over and over again was making sure that you only count where there’s a cross-join between subcategories and where you had sales. If you don’t do this, you’ll come back with the count of all subcategories (38) and you should get back the answer of 18 for your percentile. Use the COUNT function with a NONEMPTY clause to accomplish this as shown here:

MEMBER [Measures].[Count] as
    COUNT(NONEMPTY([Product].[SubCategory].Members,[Measures].[Internet Sales Amount]))

The last prep work you’ll have is to do the actual percentile formula.That formula is 1 - Ranking / Count of Members. To translate that into MDX that we can use the simple MDX below now that we’ve built all the individual calculations and set above.

Member [Measures].[Percentile] as
 1-[Measures].[SalesRank]/[Measures].[Count]
,FORMAT_STRING = 'Percent'

Lastly, just select the data out of the set and calculations using familiar MDX code:

SELECT 

     {[Measures].[SalesRank],[Measures].[Percentile],
      [Measures].[Internet Sales Amount]} on Columns, [SalesRankSet] on Rows
FROM [Adventure Works]

Here’s the complete code that works against the AdventureWorks sample cube:

WITH SET [SalesRankSet] AS
FILTER(ORDER(
[Product].[Subcategory].CHILDREN ,
   [Measures].[Internet Sales Amount],BDESC), [Measures].[Internet Sales Amount] > 0)

MEMBER [Measures].[SalesRank] AS 
     RANK([Product].[SubCategory].CurrentMember, 
                 [SalesRankSet])


MEMBER [Measures].[Count] as
    COUNT(NONEMPTY([Product].[SubCategory].Members,[Measures].[Internet Sales Amount]))

    
Member [Measures].[Percentile] as
 1-[Measures].[SalesRank]/[Measures].[Count]
,FORMAT_STRING = 'Percent'

SELECT 

     {[Measures].[SalesRank],[Measures].[Percentile],
      [Measures].[Internet Sales Amount]} on Columns,

[SalesRankSet] on Rows
FROM [Adventure Works]

Comments (5)

saaj said:

Is there way we can calculate percentile for any set created dynamically?

3/25/2010

Comment

Gbaksh said:

Hello Brian, This post helped me a lot - many thanks for providing this example. Can you please reply to what Saaj has asked. What if we have to pick up the dimension members dynamically along with the desired measure. How will be implement something like what you've posted here. Thanks!

4/25/2010

BrianKnight said:

Hi guys, to enable you to be able to pick dynamic sets, you'll have to use a scope statement to dynamically recalculate the above formula. Check out Dustin Ryan's blog post about that for more clarification and I can show that here as well.

fzfassoc said:

Would you mind giving the link to Dustin Ryan's blog post. I also need to have the percentile calculation work based on dynamic selection of members.

9/29/2011

Add a new comment

Calculating Percentile in SQL Server Analysis Services MDX

Calculating Percentile in SQL Server Analysis Services MDX

Comments (5)

Most Recent Articles