none
Percentile Question

    Question

  • Hello,

    I need to calculate percentile according to the following:

    N =(Number of vendors with a lower score)
    F =(Frequency of the vendors score – if there are no duplicate scores the value here would be 1)
    T =(Total number of vendors)

    Formula:
    (N+0.5*F)/T = Percentile Rank.
    Here’s a sample grid of 20 scores and their resulting percentile ranks:

    Score     Calculation                   %Rank
    20         19+0.5 / 20 = 0.98            98
    18         18+0.5 / 20 = 0.93            93
    16         17+0.5 / 20 = 0.88            88
    14         16+0.5 / 20 = 0.83            83
    13         13+1.5 / 20 = 0.73            73
    13         13+1.5 / 20 = 0.73            73
    13         13+1.5 / 20 = 0.73            73
    12         10+1.5 / 20 = 0.58            58
    12         10+1.5 / 20 = 0.58            58
    12         10+1.5 / 20 = 0.58            58
    11           7+1.5 / 20 = 0.43            43
    11           7+1.5 / 20 = 0.43            43
    11           7+1.5 / 20 = 0.43            43
    10              5+1 / 20 = 0.30            30
    10              5+1 / 20 = 0.30            30
    8           4 + 0.5 / 20 = 0.23            23
    6           3 + 0.5 / 20 = 0.18            18
    4           2 + 0.5 / 20 = 0.13            13
    2           1 + 0.5 / 20 = 0.08              8
    0           0 + 0.5 / 20 = 0.03              3

    How do I find the count of vendots with lower scores - N in the formula and how do I count the number of vendors with the same score - F in the formula?

    My MDX query is:

    WITH SET [ScoreRankSet] AS
    ORDER(
    [Provider].[Provider Id].CHILDREN ,
       [Measures].[Quality Score Range],BDESC)
    
    MEMBER [Measures].[ScoreRank] AS 
         RANK([Provider].[Provider Id].CurrentMember, 
                     [ScoreRankSet])
    MEMBER [Measures].[Count] as
        COUNT(NONEMPTY([Provider].[Provider Id].Members,[Measures].[Quality Score Range]))
    
    Member [Measures].[Percentile] as
     (([Measures].[Count] - [Measures].[ScoreRank])/[Measures].[Count])
    ,FORMAT_STRING = 'Percent'
    
    select  {[Measures].[Test],[Measures].[ScoreRank], [Measures].[Percentile],[Measures].[Count], [Measures].[Quality Score Range]} on 0,
    		([Provider].[Provider Id].&[339800]) on 1
    from 
    (select
    {[Provider].[Provider Id].CHILDREN} on 0,
           ({[Date].[Year-Month-Date].[Date].&[2013-08-01T00:00:00]:[Date].[Year-Month-Date].[Date].&[2013-08-31T00:00:00]},{[QA Approval Date].[Year-Month-Date].[Date].&[2013-08-01T00:00:00]:[QA Approval Date].[Year-Month-Date].[Date].&[2013-08-31T00:00:00]}) on 1         
    from [AppraisalPanelManagement] )

    Thanks,

    Itzhak

    Wednesday, September 25, 2013 8:38 PM

All replies

  • You could try the TOP or you can also set a between or however a condition..
    Wednesday, September 25, 2013 11:24 PM
  • I'm not sure what you meant.

    I have the rank and I have the total count of providers I need to get the count of providers with same score and either the count of provider with higher score ir lower score. I plugged in the actual score of a provider and I believe it gives me the right numbers but I need to do it dynamic.

    SET [ScoreRankSet] AS
    filter(ORDER(
    [Provider].[Provider Id].members ,
     [Measures].[Quality Score Range],BDESC),[Measures].[Quality Score Range] > 0)
    
    MEMBER [Measures].[ScoreRank] AS 
         RANK([Provider].[Provider Id].CurrentMember, 
              [ScoreRankSet],[Measures].[Quality Score Range]) -1
    
    MEMBER [Measures].[Count] as
        COUNT(NONEMPTY([Provider].[Provider Id].Members,[Measures].[Quality Score Range])) - 1
    
    MEMBER [Measures].[ScoreRankEqual] AS 
        count(filter([Provider].[Provider Id].members,[Measures].[Quality Score Range]  = 95))
    
    MEMBER [Measures].[ScoreRankHigher] AS 
        count(filter([Provider].[Provider Id].members,[Measures].[Quality Score Range]  > 95))

    Thanks,

    Itzhak

    Friday, September 27, 2013 12:18 AM