实现 TOPN and others 排名分类

在进行排名展示的时候，如果条目太多，可能会只看TOPN的排名，剩下的归类为其他进行展示，这种场景可以考虑用DAX或者POWER QUERY进行处理。

还是拿房产销售的数据进行演示，按销售员的销售额进行排名，只展示TOP3，剩下的归类为其他。

方法一

建立一张销售额前3的销售员名单表再加一个其他：

top3_others =
UNION ( TOPN ( 3, VALUES ( '销售表'[销售员] ), '销售表'[销售额（万元）] ),ROW ( "销售员", "其他" ) )

再建立一张销售员名单表加上其他（这里的公式用的distinct，如果用values会多一个空白行）：

业务员 =
UNION ( DISTINCT( '销售员'[销售员] ), ROW ( "销售员", "其他" ) )

建立完两张表top3_others和业务员后，写如下度量值：

top3s =
VAR t_others =
 EXCEPT ( ALL ( '业务员'[销售员] ), 
 ALL ( top3_others[销售员] ) )
VAR rest =
 CALCULATE ( '销售表'[销售额（万元）], TREATAS ( t_others, '销售员'[销售员] ) )
RETURN
 SUMX (
 top3_others,
 VARiterator = top3_others[销售员]
 RETURN
 IF (
 'top3_others'[销售员] = "其他",
 rest,
 CALCULATE ( '销售表'[销售额（万元）], '销售表'[销售员] =iterator )
 )
 )

思路是迭代top3_others这张表，如果表里的销售员名字是其他的话，就返回rest的值，反之，就返回对应销售员的销售额。变量t_others是获取其他销售员表，rest计算其他销售员的销售额，用treatas把t_others和销售员表里的销售员列建立关系进而可以筛选销售员表。

用SUMX迭代函数遍历top3_others这张表，按销售员名称进行if判断，执行对应的操作即可。

方法二

对销售记录表进行聚合，按销售员分组汇总销售额，然后对销售额进行排名。

聚合表 =
SUMMARIZE ( '销售表', '销售员'[销售员], "销售额", '销售表'[销售额（万元）] )
销售额排名 =
RANK.EQ ( '聚合表'[销售额], '聚合表'[销售额] )

接着写度量值top3s_v2：

top3s_v2 =
VAR rest =
 CALCULATE ( SUM ( '聚合表'[销售额] ), 
 FILTER ( ALL ( '聚合表' ), '聚合表'[销售额排名] >3 ) )
RETURN
 SUMX (
 top3_others,
 VARiterator = top3_others[销售员]
 RETURN
 IF (
 'top3_others'[销售员] = "其他",
 rest,
 CALCULATE ( SUM ( '聚合表'[销售额] ), '聚合表'[销售员] =iterator )
 )
 )

聚合表里排名前三之外的是其他rest，然后同方法一一样用SUMX函数对top3_others表进行遍历，迭代销售员列，按销售员名称进行IF判断，返回对应的度量值。

方法三

按照方法二的思路，用POWER QUERY进行操作。

生成第一张表——销售记录表。

把excel的销售记录表导入powerquery编辑器。按照销售员对销售额进行分类汇总。然后按销售额降序排列，添加索引（从1开始）相当于对销售额进行了排名。

然后复制两份销售记录表，一个保留前三名的记录，一个保留前三名之外的记录，最后把这两张表合并就是TOPN&others表了：

操作步骤如下：

销售记录表：

let
 源 = Excel.Workbook(File.Contents("F:数据表销售与目标.xlsx"), null, true),
 Sheet2_Sheet = 源{[Item="Sheet2",Kind="Sheet"]}[Data],
 提升的标题 = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
 更改的类型 = Table.TransformColumnTypes(提升的标题,{{"销售员", type text}, {"户型", typetext}, {"售价(万元)",Int64.Type}}),
 分组的行 = Table.Group(更改的类型, {"销售员"}, {{"销售额", eachList.Sum([#"售价(万元)"]),type number}}),
 排序的行 = Table.Sort(分组的行,{{"销售额", Order.Descending}}),
 已添加索引 = Table.AddIndexColumn(排序的行, "索引", 1, 1)
in
 已添加索引

销售记录TOP3：

let
 源 = Excel.Workbook(File.Contents("F:数据表销售与目标.xlsx"), null, true),
 Sheet2_Sheet = 源{[Item="Sheet2",Kind="Sheet"]}[Data],
 提升的标题 = Table.PromoteHeaders
 (Sheet2_Sheet, [PromoteAllScalars=true]),
 更改的类型 = Table.TransformColumnTypes(提升的标题,{{"销售员", type text}, {"户型", typetext}, {"售价(万元)",Int64.Type}}),
 分组的行 = Table.Group(更改的类型, {"销售员"}, {{"销售额", eachList.Sum([#"售价(万元)"]),type number}}),
 排序的行 = Table.Sort(分组的行,{{"销售额", Order.Descending}}),
 已添加索引 = Table.AddIndexColumn(排序的行, "索引", 1, 1),
 保留的第一行 = Table.FirstN(已添加索引,3),
 删除的列 = Table.RemoveColumns(保留的第一行,{"索引"})
in
 删除的列

销售记录其他：

let
 源 = Excel.Workbook(File.Contents("F:数据表销售与目标.xlsx"), null, true),
 Sheet2_Sheet = 源{[Item="Sheet2",Kind="Sheet"]}[Data],
 提升的标题 = Table.PromoteHeaders
 (Sheet2_Sheet, [PromoteAllScalars=true]),
 更改的类型 = Table.TransformColumnTypes(提升的标题,{{"销售员", type text}, {"户型", typetext}, {"售价(万元)",Int64.Type}}),
 分组的行 = Table.Group(更改的类型, {"销售员"}, {{"销售额", eachList.Sum([#"售价(万元)"]),type number}}),
 排序的行 = Table.Sort(分组的行,{{"销售额", Order.Descending}}),
 已添加索引 = Table.AddIndexColumn(排序的行, "索引", 1, 1),
 删除的顶端行 = Table.Skip(已添加索引,3),
 已添加自定义 = Table.AddColumn(删除的顶端行, "其他", each "其他"),
 分组的行1 = Table.Group(已添加自定义, {"其他"}, {{"销售额", each List.Sum([销售额]), type number}}),
 重命名的列 = Table.RenameColumns(分组的行1,{{"其他", "销售员"}})
in
 重命名的列

销售记录TOP3&其他：

let
 源 = Table.Combine({销售记录TOP3, 销售记录其他})
in
 源

好啦，今天的分享就到这儿，欢迎踊跃留言~

下期再见！

* PowerPivot工坊原创文章，转载请注明出处！

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

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

Power Pivot工坊