实现 TOPN and others 排名分类（续）

2019-07-02 14:04:19 ⋅ 2192 ⋅ 0 ⋅ 0

在上期《实现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的用法，不需要建立物理关系。