QQ群里面看到了一个网友的问题,是关于RANKX函数排名的问题,数据挺简单需要在三个层级做排名比较,之前写过RANKX函数的基本使用方法,实际使用过程中,也遇到过各种各样的问题,参考了各种资料,最常见的组合是:
IF+HASONVALUES+RANKX+ALLSECLECTED+BLANK。
与Excel公式一样,不同的人有不同的解题思路,最后写出来的公式也会不同,最终能够解决问题的公式就是好公式。
RANKX函数是迭代函数,所以最基本的用法是创建计算列,算是一种静态用法,根据每行的数据生成一个排名结果。
这种用法与我们在Excel中的RANK.EQ函数的用法基本一致。
另外一种用法就是用来编写度量值,这应该是一种动态的用法,度量值本身可能没有实际意义,在具体的筛选条件下会显示不同的结果。今天主要讲的就是度量值的写法,这里先不举例子了,稍后在案例中详细介绍。
除了上述的排名的用法以外,还有用作阶梯划分,就好像我们excel中使用LOOKUP函数做数据分组的用法,大家可以网上查资料参考一下。
从以往的使用经验来看,正值排名很简单,几乎没出过问题,虽然RANKX函数有5个参数之多,但是实际上我们只用前两个就够了,除非要改变顺序,排名的紧密度,才要修改第四、第五参数,而第三参数,很少用到。
数据如下图,有空值,没有负值,需要对各级部门进行排序,排序依据就是利润:
我们把数据加载到Power Pivot 或者Power BI Desktop都可以,我用的是Power BI Desktop:
需要写的排名度量值有以下几个:
· 一级:一级整体排名
· 二级all:二级整体排名
· 二级in一级:二级在所属的一级下的排名
· 三级all:三级的整体排名
· 三级in一级:三级在一级部门下的排名
· 三级in二级:三级在二级部门下的排名
最后要求全部度量值体现在一张矩阵表中。
怎么才能够让这些度量值正确的显示在正确的位置?这需要用到一个信息函数ISINSCOPE函数,这个函数的作用就是判断在矩阵表中所处的层级位置,我们写三个度量值来测试一下:
is1 = ISINSCOPE(data[一级部门])
is2 = ISINSCOPE(data[二级部门])
is3 = ISINSCOPE(data[三级部门])
这些TRUE与FALSE就为我们写度量值提供了依据,如果我们要在三级部门所在的行显示度量值,只需要加一个判断:
if( ISINSCOPE(data[三级部门])
就可以了,是不是很方便,其他稍微复杂一点不过就是加上AND/NOT等逻辑函数。
还有一个问题,我们要用RANKX度量值,就不能像写计算列那样,直接引用列,而是需要先写一个利润列的聚合度量值,一下的操作我们都在data2这个正值的表中操作:
S2 = sum('data2'[利润])
正值的RANKX都不复杂,我们先从最简单的开始:一级排名:
一级2 =
IF (
ISINSCOPE ( 'data2'[一级部门])&&NOT(ISINSCOPE ( 'data2'[二级部门]))&&NOT(ISINSCOPE(data2[三级部门] )),
VAR a = [S2]
VAR prank =
RANKX (
ALLSELECTED(data2[一级部门]),
[S2]
)
VAR re =
IF (
NOT ISBLANK ( a ),
prank
)
RETURN
re
)
文章开头我们说过,比较常见的组合是:
IF+HASONVALUES+RANKX+ALLSECLECTED+BLANK
这里我们用IF+ISINSCOPE替换了IF+HASONVALUES
在结尾处的NOT ISBLANK ( a )有效的过滤了空值。
其他几个排名我们就参照这个写,不同之处有两处:
· IF+ISINSCOPE:显示的位置不同
· ALLSELECTED:筛选的层级不同
我就不一一引用了,给大家截几个图:
三级in二级我写了两种,给大家截的这个简单。
到这里正值排名基本结束了,我们把数据源稍作修改来看看负值排名的结果:
我写的那个复杂的三级in二级居然没出问题,我红色箭头标出的就是出问题的地方,负值排名变得很大,不是我们预期的123,变成了128,问题出在哪里?这是RANKX函数的BUG么?
我查了一些资料,但是都没有明确的答案,不过我猜想,排名变大,是因为空值的关系,RANKX函数默认是正值排名,当出现多层级筛选时,空值被认为是0值参与排名,所以负值排名就变大。
怎么去掉零值呢,我们做个小实验:
我们在第一参数上加一个FILTER函数,过滤0值,0值与第一个负值排名相同,改变了负值的排名。
我们用同样的方法,在正值的排名度量值基础上,增加一个过滤器,先过滤掉0值,然后再排名,看看能不能解决问题,以下我们在data表进行操作,[利润S]是预先写好的利润聚合度量值:
二级in一级 =
IF (
ISINSCOPE ( 'data'[二级部门])&&NOT(ISINSCOPE(data[三级部门] )),
VAR s = [利润S]
VAR PRank =
RANKX (
FILTER(ALLSELECTED(data[二级部门]),[利润S]<>0),
[利润S]
)
VAR re =
IF (
NOT ISBLANK ( s ),
PRank
)
RETURN
re
)
同样的道理,我们对出问题的几个度量值做修改,查看结果:
负值也能够正确参与排名了。
综上所述,RANKX还是有点小问题的,问题就出在空值做0值参与排序,全是正值的时候,0值被排除,当有负值出现,0值参与排名,导致排名结果出现异常。
我们写了这么多,那名网友的问题没有完全解答,我们在想尽办法去除空值,他有一项要求是空值参与排名,这个还真有待研究。
页面更新:2024-03-20
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号