Calculating Percentile using Rank and Count

[Sid10]

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 · Accepted solution]

Hi @Sid10 ,

try this.

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

[Sid10]

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)

[Tahreem24]

I am sorry @Sid10 just got time to see your post.

It's great @mwegener has already resolved your issue.