DAX 函数之百分位计算

​说到百分位，不得不提箱线图，也就是股市最常见到的图表之一，在统计学上有5个数据组成了箱线图的数据，包括最大值、最小值、中值，以及两个四分位数（包括1/4分位和3/4分位），今天我们介绍一下分位数中的扩展：百分位数。
​百分位数的计算方法

1、百分位的概念

统计学术语，如果将一组数据从小到大排序，并计算相应的累计百分位，则某一百分位所对应数据的值就称为这一百分位的百分位数。可表示为：一组n个观测值按数值大小排列。如，处于p%位置的值称第p百分位数。

2、百分位数计算方法

在DAX函数中，关于百分位数的计算有两个，分别是PERCENTILE.EXC以及PERCENTILE.INC，这两个函数的算法是有区别的，我们先来看看计算的结果。

先准备一组数据，如下图所示：

为了缩小数据的范围，我们将销售明细和品类码表做关联后，只做Accessories品类的计算，因此可以在报表中将品类拖拽到一个筛选器上，并筛选第一个选项，如下图所示：

创建两个度量值，表达式分别是：

百分比_EXC = PERCENTILE.EXC('ResellerSales_USD'[SalesAmount_USD],0.1)

以及

百分比_INC = PERCENTILE.INC('ResellerSales_USD'[SalesAmount_USD],0.1)

创建完毕后，将这两个度量值拖拽到一个多行卡可视化对象中，效果如下图所示：

也就是说，品类是Accessories，计算销售额的10%分位的值分别是2,162.90和2,192.20，其中PERCENTILE.EXC 这个函数计算出来的结果是2,162.90，这两组度量值计算出来的结果是否准确呢？我们先了解一下这两个函数的计算原来，以及为了方便验证，我们到Excel 表中按照计算原理来验证。

需要注意的是，k值虽然说是百分比，但是不能使用百分比来输入，如

百分比_EXC = PERCENTILE.EXC('ResellerSales_USD'[SalesAmount_USD],0.1)中的k值为0.1，就不能输入10%。

3、计算原理

下面我们来说说这两个函数的计算原理，其中在Power BI Desktop 中的计算原理，和在Excel 中是一样的。

（1）PERCENTILE.INC(array,k) 描述性解释

将数组array从小到大排序（升序），设n为数组元素个数，计算出k*(n-1)的整数部分为a，小数部分为b，则PERCENTILE.INC(array,k)的值是：array第a+1个数×(1-b)+array第a+2个数×b。

（2）PERCENTILE.EXC(array,k) 描述性解释

将数组array从小到大排序（升序），设n为数组元素个数，计算出k*(n-1)的整数部分为a，小数部分为b，则PERCENTILE.INC(array,k)的值是：array第a个数×(1-b)+array第a+1个数×b。

我们用PERCENTILE.EXC 函数的计算原来，用上述的数据，到Excel 中来验证一下。首先我们先对销售额列排序，并在B列做一个序号标识。

第1步：n值

函数：=COUNTA(A2:A279)，其中array 为A2:A279

计算结果：278

效果如下图所示：

第2步：k值

函数：这里我们已经设置为固定的0.1，即10%

效果如下图所示：

第3步：k*(n+1)值

函数：=J11*(J10+1)

计算结果：27.9

效果如下图所示：

第4步：整数部分

函数：=TRUNC(J12)

计算结果：27

效果如下图所示：

第5步：小数部分

函数：=J12-J13

计算结果：0.9

效果如下图所示：

第6步：计算PERCENTILE.EXC 的结果

函数：=A28*(1-J14)+A29*J14

计算结果：2162.9，其中A28为array第a个数，即第27个数据

效果如下图所示：

这个数据和我们在Power BI Desktop 中的计算结果是一致的，效果如下图所示：

PERCENTILE.INC 这个函数的计算方法也类似，有兴趣的朋友可以动手按照函数的计算原理验证一下，在这里就不再赘述了。

4、另一种算法

在Power BI Desktop 中，计算百分位数，除了PERCENTILE.EXC、PERCENTILE.INC这两个函数之外，还有两个函数，分别是PERCENTILEX.EXC和PERCENTILEX.INC，这两组函数的区别在于他们的参数引用范围一样，前者引用的是列，而后者引用的是表+列，计算的结果是一样的，有兴趣的朋友也可以自己动手验证一下。

在Excel 中，关于百分位的计算还有一组逆计算函数，包括PERCENTRANK.EXC和PERCENTRANK.INC，他们分别是PERCENTILE.EXC、PERCENTILE.INC这两个函数的逆序计算，但是注意的是，这组函数目前在Power BI Desktop 中尚没有。

百分位数在日常分析中应用还是比较广泛的，除了开始提到的箱线图，我们还有其他的场景可以用到，比如我们要看一个班级里边70%的学生分数都超过多少分，此时我们就可以使用到百分位数的计算函数。