Getting different Percentile results when using two different software

I'm calculating 90th percentile values in two different places using two different pieces of software, and I'm getting different results.

These results need to be comparable. I understand there are different ways to calculate Percentile, but this isn't my area of expertise.

The two software is Power BI (using PERCENTILE.INC) and Math.NET C# library (Statistics.Percentile)

I've made sure that the data being fed to both methods are the same. For example, using the following set: 49,33,28,33,21.1,9,0

• Power BI comes back with 39.4
• Math.NET comes back with 47.9

Can anybody tell me why these are different? Or what I should look for to get comparable results?

• Sample size = 8, 90th percentile ==> 8*0.9 = 7.2 ==> the estimate should be any number between 7th and 8th. (In you case, between 33 and 49). Different software has different rules to pick up a number between between 33 and 49. Commented Aug 8, 2019 at 14:17

As you say, there are several different ways to define quantiles.

For your small sample: Your sorted data are as follows (from R):

x = c(49,33,28,33,21.1,9,0)
sort(x)
[1]  0.0  9.0 21.1 28.0 33.0 33.0 49.0

With this small sample of $$n = 7$$ observations, there is no one obvious choice for the 90th percentile. Various statistical software programs make different compromises.

The documentation of R's quantile procedure lists nine different rules for such compromises accessible with the parameter type. Exact formulas and rationales for most of them are shown there. (R provides the opportunity to use different types of quantiles for compatibility with other software programs.)

Type 7, the default in R, gives one of the values for the 90th percentile that you mentioned.

quantile(x, .9)   # usually, no 'type' specified, R uses Type 7
90%
39.4
quantile(x, .9, type=7)
90%
39.4

Your other value results from using Type 9:

quantile(x, .9, type=9)
90%
47.4

Because of the large gap between observations 33 and 49, a large variety of other values are possible with different types, including 33 (from Type 3) and 49 (from Types 1 and 2).

Larger samples: In practice, quantiles are mostly used for samples of moderate and large size, for which the different rules don't make substantial differences.

y = sort(rnorm(1000, 50, 7))
quantile(y, .9)
90%
59.01486
quantile(y, .9, type=1)
90%
59.01104
quantile(y, .9, type=3)
90%
59.01104
y[898:902]
[1] 59.00132 59.01043 59.01104 59.04926 59.09998

Consequences of rounding. Rounding a sample to integers can induce many ties, and thus fewer differences among the rules:

z = sort(round(rnorm(1000, 50, 7)))
quantile(z, .9)
90%
59
quantile(z, .9, type=1)
90%
59
quantile(z, .9, type=3)
90%
59
z[898:902]
[1] 59 59 59 59 60

When you are taking a basic statistics course, use whatever rule is given in your text or recommended by your instructor. But be aware that if you use software to find answers, you won't necessarily get exactly the same answer as an answer book.

In practice, you will probably use a particular kind of statistical software, and be happy enough with whatever rule the software uses as its default.

Addendum per comments about application: If your samples are coming from a normal population, it may be best to use the fact that the sample mean $$\bar X$$ and sample SD $$S_X$$ are based on sufficient statistics, so tend to provide better information about the normal population than do quantiles. Then the procedure would be to find the 90th percentile of the normal distribution $$\mathsf{Norm}(\bar X, S_X).$$

In the simulation below, I compare the actual 90th quartiles q.9 with estimates q.e obtained under the assumption of normality. The latter estimates do a little better. If the population distribution is $$\mathsf{Norm}(\mu = 25. \sigma = 5),$$ then its 90th percentile is 13.41. Also, in the simulation I tried Type 9 quantiles which are said to give unbiased approximations of population quantiles of normal distributions.

qnorm(.9, 25, 5)
[1] 31.40776

set.seed(808)
m = 10^5;  n=40;  mu=25; sg=5
q.9 = q.e = numeric(m)

for(i in 1:m) {
x = rnorm(n, mu, sg)
q.e[i] = qnorm(.9, mean(x), sd(x))
q.9[i] = quantile(x, .9, type=9)   }
mean(q.e);  sd(q.e)
[1] 31.36575        # aprx 31.41
[1] 1.072285        # less variable than below
mean(q.9);  sd(q.9)
[1] 31.4376         # aprx 31.41
[1] 1.302801        # more variable than above

Both methods tend to give nearly the right 90th percentile on average, but the method using the normal distribution is more consistently close to the correct answer. Root mean square error (RMSE) is one way to measure 'goodness' of an estimator, low values are better. The RMSE using quantiles is a little larger, but this method does not assume normality. (RMSE from Type 7 quantiles is about 2.87.)

sqrt(mean((q.e-31.41)^2))
[1] 1.073193               # smaller RMSE
sqrt(mean((q.9-31.41)^2))
[1] 1.303086               # larger

Something similar might be done for sampling from an exponential population, but if you have no idea what kind of distribution the population has (or even whether it is stable), then using sample quantiles to estimate population quantiles may be a safer course of action.

• Thank you, this has really helped. For the record, this is for a practical application. Power BI supports R so I should be able to play around with the different type parameters and get something I'm happy with. If only other libraries were that robust! Commented Aug 8, 2019 at 22:50
• Is your goal to estimate the 90th percentile of some normal process based on a small sample x? If so, is 7 a typical sample size? Then you might do better to find the avg a and the sd s of the sample and then do qnorm(.9, a, s). There is 'better info' about the population dist'n in a and s than in the 90th percentile. Commented Aug 9, 2019 at 1:23
• The sample size varies but can be as low as my example and as high as 80. The goal is to generate an urgent limit line on a graph based on user fleet data. Commented Aug 9, 2019 at 1:59
• Probably OK to use sample quantiles. See Addendum to my Answer. Commented Aug 9, 2019 at 2:35