Forum Discussion

Sid10's avatar
Sid10
Helper I
5 years ago
Solved

Calculating Percentile using Rank and Count

We know that Percentile = Rank/(n+1), where n is the number of values for which the percentiles are being calculated. 

This has worked fine for me in powerbi when the values are distinct. However, if two or more values are similar then the Rank and Percentile fails as shown in the Table below called CQM. E.g. more there many rows with a % score of 0%, and 14.29%. This causes the percentile calculation to mess up (obviously percentiles cannot exceed 100% as is shown in the table below). 

Is there any suggested fix for this? Great appreciate your help. Thank you. 

 

Further details for reference:

Formula for Rank and Percentile are as shown:
LowBirthWt_Rank = IF(ISBLANK([LowBirthWt%_m]), BLANK(), RANKX(ALL(CQM), CALCULATE(SUMX(CQM, [LowBirthWt%_m])), ,ASC, Skip))
LowBirthWt_%ile = DIVIDE([LowBirthWt_Rank], COUNTX(ALL(CQM), CQM[LowBirthWt%_m])+1)

See below:

Requesting Tahreem24 or anybody else please. 

  • mwegener's avatar
    mwegener
    5 years ago

    Hi Sid10 ,

     

    try this.

     

    LowBirthWt_%ile = DIVIDE([LowBirthWt_Rank], COUNTX(ALL(CQM), CQM[GrantNumber]))

     

3 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    5 years ago

    Hi Sid10 ,

     

    try this.

     

    LowBirthWt_%ile = DIVIDE([LowBirthWt_Rank], COUNTX(ALL(CQM), CQM[GrantNumber]))

     

    • Sid10's avatar
      Sid10
      Helper I
      5 years ago

      Wow! That did it! Thanks a lot.

      Just a minor correction. Your formula should be:

      LowBirthWt_%ile = DIVIDE([LowBirthWt_Rank],COUNTX(ALL(CQM),CQM[GrantNumber])+1)