DAX中如何实现动态TOPN and Others

显示全部楼层 · 2021-2-22 14:17:23

这是一个来自群里一位学员的提问，问题有一定的实际应用价值，值得研究一下。

使用视觉级筛选器实现 TOPN

我们已经可以方便的用视觉筛选器筛选出 topn，但还不能把剩余成员归入一个单独的分组。

可以创建计算列生成排名，根据排名来分组，这是一种静态方式。即不能感知外部筛选条件，比如从看 top5 变为看 top10，成员的数量变化，需要重新定义计算列；从查看全局的 top5 变为只看某个地区的 top5，也要重新定义计算列。

静态分组示意图

鉴于静态分组缺少灵活性，实现动态分组无疑更有价值。因为表格和图表的展现方式有所不同，我介绍两种方法。

数据源是一张超市订单表，根据销售额统计 topn 顾客和 others

新建一个 TOPN 表，用于存放排名

表格
表格可以显示多个列，适用于组别和组内的成员同时展现的场景

遗憾的是目前表格和矩阵视图都不支持折叠组，我用另外一个自定义控件 AP Matrix 模拟了折叠后的效果。

如果是 Excel 透视表，你可以发挥最大的灵活性，自由地折叠和展开

因为数据源不包含分组名称，先创建一个虚拟表 group

topx 存放筛选器选出的前 x 名顾客，其他顾客放入 others，如何实现这一步是解决问题的关键。用一句话描述：为每个顾客计算排名，排名小于等于 x 的归入 topx 组，排名大于 x 的归入 others 组。公式如下

提示：公式生效的前提是 group 和客户名称都放到行，否则 selectedvalue 返回空值，整个公式也为空。

每个组都对所有顾客计值，但只返回符合要求的顾客
对于 filter 内部而言，公式只计算一个客户，即外部上下文限定的客户，计算他在所有顾客中的销售额排名，并判断是否符合限定条件。
如果只计算某个地区的特定排名，只需要添加一个地区筛选器，不需要修改公式，Rankx 中[销售额]会感知外部筛选条件的变化

这个方法最早由 Rob Collie 于 2014 年分享在自己的博客上，名为“Displaying Top N, Bottom N, and “All Others”. 后来 Excelhome 上有一个帖子也用了类似的方法，但名字已经记不得了。

这是一种很巧妙的解法，尤其适合在 excel 里使用，值得掌握，但不适合用于所有可视化场景，因为在图表里通常需要把 topx 明细和 others 组放到一起展示：

如果图表的 Y 轴能像透视表那样可以选择性折叠，情况就简单多了，直接套用上面的公式就能解决问题。但目前我们不得不换一种方法。

图表
顾客列表中原本没有 others，为了实现最终效果，新建一个包含 others 的顾客虚拟表：

客户列表+others = UNION(VALUES(‘订单'[客户名称]),ROW(“客户名称”,”其他客户”))

新表保持独立，不与原表建立关系，先来看下最终效果

左边的图用了一个度量值，右边和下边的图使用了 topx 和 others 两个度量值，这么做的好处是保证 top 顾客按销售额降序排列的同时，其他客户这个分组可以位于最下面，还能指定不同的颜色。

下面的柱形图使用了双 Y 轴，看起来不像上面那么突兀了，不过目前支持双 Y 轴的图表不多，只有 5 种，不包含内置的条形图和柱形图。

左图使用的度量值

右图和下图使用的度量值

提示：图表用的度量值写法和表格几乎相同，只是多了一步用 TREATAS 映射虚拟顾客列表和模型中的顾客列表（’订单'[客户名称]）

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi
如果你感兴趣，可以研究一下这个帖子，作者给出了另外一种写法，只是过于复杂。

从条形图效果上看，如果其他顾客组的总销售额过大，top 顾客会变得难以辨认，所以请根据实际情况选择合适的图表。