在PowerPIvot / DAX中获得PERCENTRANK.INC [英] Getting the PERCENTRANK.INC in PowerPIvot/DAX

问题描述

我的任务是将工作表从标准Excel转换为PowerPivot。但是，我遇到了PERCENTRANK.INC函数的障碍，DAX中不提供此函数。我已经接近使用公式来复制它，但是在计算上存在一定的差异。

I have been tasked with converting a a worksheet from standard Excel into PowerPivot. However, I have hit a roadblock with the PERCENTRANK.INC function which is not available in DAX. I have come close to replicating it using a formula, but there are definite differences in the calculation.

有人知道Excel如何计算PERCENTRANK.INC（）吗？

Does anyone know how Excel calculates PERCENTRANK.INC()?

Formula in cell D2: =(COUNT($A$2:$A$10)-B2)/(COUNT($A$2:$A$10)-1)
Formula in cell B2: =RANK.EQ(A2,$A$2:$A$10,0)
Formula in cell C2: =PERCENTRANK.INC($A$2:$A$10,A2)

推荐答案

编辑：

我觉得奇怪的是，有太多标准方法来计算 PERCENTRANK 不同的结果。

使用您的9个数字集 1,2,3,4,4,6,7,8， 9 ，根据我使用的是哪个权限，第三值（ 3 ）的百分比排名为 25.0％，27.0％，27.8％或30.0％。

Using your example of your 9-number set of 1,2,3,4,4,6,7,8,9, depending on which "authority" I used, the third value (3) had a Percent Rank of 25.0%, 27.0%, 27.8% or 30.0%.

很显然，我们会提供与您期望的结果相匹配的，匹配 PERCENTRANK.INC 。

Obviously we'll go with the one that gives your desired result, matching PERCENTRANK.INC.

PERCENTRANK.INC 的计算公式为：

[小于给定值的值计数]

÷

< h2> [集合中所有不包含给定值的值的计数]

   PERCENTRANK.INC is calculated as:

          [count of values lower than the given value]

                             ÷

    [count of all values in the set excluding the given value]

因此，如果我们的范围 1,2,3,4,4,6,7,8,9 在 A1：A9 中，我们可以在 B1 ：

so, if our range of 1,2,3,4,4,6,7,8,9is in A1:A9, we could use this formula in B1:

=COUNTIF($A$1:$A$9,"<"&A1)/(COUNT($A$1:$A$9)-1)

...然后复制或填充下来以得到结果：

...and copy or "fill" it down for results:

0.0%, 12.5%, 25.0%, 37.5%, 37.5%, 62.5%, 75.0%, 87.5%, 100.0%

---

原始答案

---

Original Answer

我想您只想计算 PERCENTRANK 当前行
的值。根据 PERCENTRANK 的逻辑，我们可以在
表中添加 RANK 列并实现基于在此列上。

I think you just want to calculate the PERCENTRANK for current row value. Based on the logic for PERCENTRANK, we can add a RANK column in table and achieve same logic based on this column.

创建排名列。

Create a Rank column.

Rank = RANKX(Table6,Table6[Value])

然后根据排名列创建 PctRank 。

Then create the PctRank based on the Rank column.

PctRank = (COUNTA(Table6[Name])-Table6[Rank])/(COUNTA(Table6[Name])-1)

^（来源）

这篇关于在PowerPIvot / DAX中获得PERCENTRANK.INC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！