# 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