从数据到信息
从信息到洞察

DAX中如何实现动态TOPN and Others

假如我有非常多分类,但是我只想展示某度量值的 top5,然后其它类别想汇总显示为其它,应该怎么做?

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

TOPN and Others

使用视觉级筛选器实现 TOPN

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

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

TOPN and Others

静态分组示意图

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

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

新建一个 TOPN 表,用于存放排名TOPN and Others

表格

表格可以显示多个列,适用于组别和组内的成员同时展现的场景
TOPN and Others
遗憾的是目前表格和矩阵视图都不支持折叠组,我用另外一个自定义控件 AP Matrix 模拟了折叠后的效果。

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

效果示意图

因为数据源不包含分组名称,先创建一个虚拟表 group
TOPN and Others
topx 存放筛选器选出的前 x 名顾客,其他顾客放入 others,如何实现这一步是解决问题的关键。用一句话描述:为每个顾客计算排名,排名小于等于 x 的归入 topx 组,排名大于 x 的归入 others 组。公式如下

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

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

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

这是一种很巧妙的解法,尤其适合在 excel 里使用,值得掌握,但不适合用于所有可视化场景,因为在图表里通常需要把 topx 明细和 others 组放到一起展示:
TOPN and Others
如果图表的 Y 轴能像透视表那样可以选择性折叠,情况就简单多了,直接套用上面的公式就能解决问题。但目前我们不得不换一种方法。

图表

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

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

新表保持独立,不与原表建立关系,先来看下最终效果
TOPN and Others
左边的图用了一个度量值,右边和下边的图使用了 topx 和 others 两个度量值,这么做的好处是保证 top 顾客按销售额降序排列的同时,其他客户这个分组可以位于最下面,还能指定不同的颜色。

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

左图使用的度量值
TOPN and Others

右图和下图使用的度量值
TOPN and Others
TOPN and Others
提示:图表用的度量值写法和表格几乎相同,只是多了一步用 TREATAS 映射虚拟顾客列表和模型中的顾客列表(’订单'[客户名称])

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

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

11
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
5 评论数
6 被回复的评论
4 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
游客
kk

group是动态变化的,如何将group 和客户名称都放到行,求解~~谢谢老师 悲伤

成员
xifeng

图片上第六行的HASONEVALUE在总计行时是FALSE,那么第七行的VALUES就有多个值,这样不会出错的么,我尝试过这样的度量值:IF(VALUES(‘表'[列]))=值,TRUE值,FALSE值),结果报错了。是因为我用的数据透视表,而图片上的例子是图么,希望老师能帮我解惑,谢谢~

7-1553731728.jpg
游客
bank

有案例文件下载链接嘛?

游客
bi小白

测试下回复功能,应该是还没有搭建好用户管理系统

游客
畅心

有个场景可以用这种方式,看最后几名然后对比整体平均值情况。