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

DAX中如何实现动态TOPN and Others

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

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

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

使用视觉级筛选器实现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顾客会变得难以辨认,所以请根据实际情况选择合适的图表。

原创内容 转载请联系作者:PowerBI极客 » DAX中如何实现动态TOPN and Others

评论 3

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  1. #2

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

    bi小白6天前回复
    • 已经搭好了

      高飞6天前
  2. #1

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

    畅心6天前回复