在数据分析的过程中,我们经常要对某一个维度进行由多到少汇总统计。而当维度项目过多时,可能需要使用top5或者top10 来展现。之前实现的方式就是在filter里添加一个筛选器TOPN。 但是如果我们希望这个N是可以动态调整的,同时将非TOPN得项目合并到Other里面并统计。这个时候难度就增大了。从网上搜索了一些资源进行学习,记录下这个思路。
先展示下生成的效果图。
这里汇总值可以调整,同时要展示前几个项目也是依据动态参数来实现的。
实现思路
辅助表
汇总值以及动态参数N和柱状图的X轴都是一个计算表内的值。如果是一个单独的DAX写出来的是无法拖动为筛选器或者图上的。所以我们需要必须要手动制作三个辅助表。
筛选器N的是最简单实现,可以直接使用参数来生成。
柱状图X轴则需要使用DAX来实现,在原有的维度里面是没有Other这个项目的。所以要额外加入。
1
2ALL_Brand = UNION(VALUES('Product'[Brand]),{"Other"})
//使用values获取表里brand唯一值,然后用Union将Other加入新表中汇总值的切换有两种方式,一种是使用动态参数去勾选。 一种是直接用DAX实现。
1
2
3
4
5
6
7
8KPI指标 = SELECTCOLUMNS({("销售额",1),("销量",2),("利润",3),("利润率",4)},"KPI",[Value1],"OrderBy",[Value2])
//使用动态参数生成结果如下
KPI2 = {
("利润", NAMEOF('Measure_Top'[利润]), 0),
("利润率", NAMEOF('Measure_Top'[利润率]), 1),
("销售额", NAMEOF('Measure_Top'[销售额]), 2),
("销量", NAMEOF('Measure_Top'[销量]), 3)
}无论是直接生成的还是手动写的DAX,都无法直接引用,都需要写一个中转最终的度量值函数用于后续计算,
1
2
3
4
5
6
7
8KPI.auto = //返回对应的指标度量值
var KPINAME=SELECTEDVALUE('KPI指标'[KPI])
return
SWITCH(KPINAME,
"销售额",[销售额],
"销量",[销量],
"利润",[利润],
"利润率",[利润率])
柱状图表实现思路
图表的实现,其实是利用如果Y轴的结果为空的时候,图形就不会显示。参考下表展示结果,正常的求和是KPI_auto,在图形展示时,view_kpi.auto将不在top的结果都汇总到other里面。所以不会显示结果。同时我们需要将other的rank调到一个很大的值,避免和正常的n有冲突。
这里的dax实现是首先对汇总值进行排名,其次根据有排名名次和选择的n去比对,返回不同的汇总值。
1 | KPI.rank = //对相应的指标进行排名 |
当我们选择n=4时,view_kpi.auto 对应的other返回的值就是rank大于4所有的KPI.auto求和。这里我们需要就不得不感叹calculate函数的强大,直接修改了汇总上下文。
当图表生成后可能展示的结果并没有按照我们的需求来排序,即TOP1-TOP4 然后显示Other,因为正常的排序要么按照x轴字母排序,要么按照汇总量排序,而other的汇总往往是大于top里面的值。所以另外一个巧妙的地方就是额外加一个view_kpi_rank
度量值用于图形的排序。逻辑就是如果在n范围内正常返回,否则就把other设置为50. 这样我们可以将图形按照这个度量值排序,就可以正常显示了。需要将度量值放入tooltips里面,才可以配置图形的排序
1 | view_kpi_rank = //展示显示排名 |
累积折线图实现
- 先统计一个所有的汇总值用于求占比
- 针对每个显示的项目进行占比统计
View_kpi.percent
- 求累积占比,由于other在最后一个所有直接配置为100%, 其余部分通过calculate修改上下文,用view_kpi_rank来判定 生成累积占比。
1
2
3
4
5
6
7
8
9
10//所有brand求和
View_KPI_ALL = CALCULATE([KPI.auto],ALL(ALL_Brand[Brand]))
//当前占比
View_kpi.percent = DIVIDE([View_kpi.auto],[View_KPI_ALL])
//累积占比
View_Kpi.per.acc =
var cur=[view_kpi_rank]
var brand=SELECTEDVALUE(ALL_Brand[Brand])
return
if(brand="Other",1,CALCULATE([View_kpi.percent],FILTER(all(ALL_Brand[Brand]),and([view_kpi_rank]<=cur,[view_kpi_rank]>0))))
学习总结
- 度量值的命名
这里的度量值命名方式在实际使用中很方式,比如view
开头可以直接识别出来时用于图形展示。另外可以用点来匹配。当我们使用很多度量值的时候,引用起来更方便 - 度量值的简化和嵌套
这里的基本度量值像销售额
,成本
等,在后面时逐步被引用的,先到KPI然后再到汇总求和,再到前台展示引用。所以我们有时不需要将度量值写的太复杂,需要拆分到最小单元。 - Calculate的使用,在用于求累积比例和others的汇总时,都有用到这个函数,去修改上下文条件。目前的理解还不够透彻。需要自己再去多尝试。
- 文中的利润率实际上展示会有异常,仅用于测试,因为other的不应该是100%.
本文数据使用的是powerbi教材的demo数据,各个度量值命名和写法来自知乎的文章:https://zhuanlan.zhihu.com/p/406365130