实现 TOPN and others 排名分类(续)


在上期《实现TOPN and others排名分类》一文中,有坊友反映文中的实现方法较适用于静态的场景,迭代的top3_others表是固定的,基于销售表整体而言没有动态性,如果加上年份,地区切片筛选就不适用了。比如我有一张销售记录表A,表A有很多维度字段,我迭代top5_others这张单列表(销售员列,按销售额排名),如果筛选器把表A按大区筛选,选北区,那就是北区的top5_others,再选年,18年北区的top5_others,那top5_others也需要跟着变!

今天解决的场景就是TOPN and others能够随年份/地区等维度切片而动态变化。维度表与事实表关联,维度变,事实表跟着变。如果topN_others要想跟着维度表变化,只要把topN_others与事实表相关联,这样,维度表变,事实表变,进而,topN_others也跟着变。

基于这样的思路,先用第一种方法TREATAS解决。

方法一

还是以房产销售的场景为例,先创建一张销售员名单表加上“其他”行命名为name_list表:

name_list =
UNION ( VALUES( '销售表'[销售员] ), ROW ( "销售员", "其他" ) )

file

创建度量值:

top3_new =
IF (
    NOT ( ISFILTERED ( 'name_list'[销售员] ) ),
    '销售表'[salesAmout],
    IF (
        SELECTEDVALUE( name_list[销售员] ) = "其他",
        CALCULATE(
            '销售表'[salesAmout],
            FILTER (
                VALUES ( '销售表'[销售员] ),
                RANKX ( ALL ( '销售表'[销售员] ), '销售表'[salesAmout] ) > 3
            )
        ),
        CALCULATE(
            '销售表'[salesAmout],
            FILTER (
                TREATAS ( VALUES ( 'name_list'[销售员] ), '销售表'[销售员] ),
                RANKX ( ALL ( '销售表'[销售员] ), '销售表'[salesAmout] ) <= 3
            )
        )
    )
)

name_list表里的销售员列如果没有筛选就返回'销售表'[salesAmout]度量值,这里相当于返回总计行的值;如果有了筛选,进一步判断,如果筛选的值是其他的话,就返回其他的值,反之,返回TOPN的值。

其他的值判断的条件是对销售记录表的所有销售员按销售额进行排名,如果排名大于3则为其他,聚合对应的销售额。如果排名小于等于3,则按对应销售员聚合销售额返回。

这里用TREATAS把name_list表的销售员和销售表里的销售员进行了关联,相当于建立了关系,name_list表的销售员列可以对销售表进行筛选,从而可以返回TOP3销售员的销售额。

方法二

第二种方法不用SELECTEDVALUE和TREATAS解决,因为有时候电脑安装的SSAS版本恰好没有这两个函数,只能另辟蹊径。
同方法一,创建一张包含其他的name_list表,命名为name_list_v3:

name_list_v3 =
UNION ( VALUES( '销售表'[销售员] ), ROW ( "销售员", "其他" ) )

创建度量值:

top3_new_v3 =
SUMX (
    'name_list_v3',
    VAR rest =
        CALCULATE(
            '销售表'[salesAmout],
            FILTER (
                VALUES ( '销售表'[销售员] ),
                RANKX ( ALL ( '销售表'[销售员] ), '销售表'[salesAmout] ) > 3
            )
        )
    RETURN
        IF(
            'name_list_v3'[销售员] = "其他",
            rest,
            CALCULATE (
                '销售表'[salesAmout],
                FILTER (
                    VALUES ( '销售表'[销售员] ),
                    RANKX ( ALL ( '销售表'[销售员] ), '销售表'[salesAmout] ) <= 3
                        && '销售表'[销售员] = 'name_list_v3'[销售员]
                )
            )
        )
)

这次用的是SUMX迭代函数,遍历name_list销售员表,如果name_list等于其他,则返回rest,反之,返回TOP3对应的销售员的销售额。度量中创建rest变量,对应其他的销售员的销售额,非其他的,用filter进行迭代,遍历VALUES ( '销售表'[销售员] ),销售表里的销售员,条件是销售员的销售额排名小于等于3,并且(接下来的重点)'销售表'[销售员] = 'name_list_v3'[销售员],这个等号表达式把销售表里的销售员和name_list_v3表里的销售员进行了关联,代替了TREATAS的用法,不需要建立物理关系。

因为用的是FILTER迭代器函数(行筛选),所以两个列可以用等号进行关联匹配,实现了name_list_v3的销售员筛选销售表里的销售记录,把对应的销售记录按SUMX迭代的name_list_v3销售员进行聚合,得到相应的结果。

两种方法都讲完了,现在测试一下效果:

file

从图中我们可以看到,北区2019年销售员TOP3&others的结果,和在左侧整体表的结果是一样的,没有问题,测试成功!
今天就分享到这里,希望对大家有所帮助,多谢~



* PowerPivot工坊原创文章,转载请注明出处!


如果您想深入学习微软Power BI,欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号(PowerPivot工坊)后猛戳”在线学习”。


长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯,欢迎小伙伴儿们转发分享~

Power Pivot工坊