在商业智能分析中,经常需要同时展示数据的宏观汇总和微观细节,比如同时查看年度汇总数据和季度明细数据,以便从宏观和微观两个层面把握业务趋势。
本篇文章将介绍如何在PowerBI中绘制这种同时展示所选年份的年汇总与季度明细的柱形图。
问题描述
绘制同时展示所选年份的年汇总与季度明细的柱形图,效果如下图所示:
本案例所用的初始数据是使用DAX函数生成的计算表,如下图所示:
计算表的表达式如下:
Fact = ADDCOLUMNS(CALENDAR(dt"2016-1-1",dt"2022-1-1"),"Value",RANDBETWEEN(1,100))
解题要点
由于需要在柱形图的单个X轴字段中同时展示年份与季度,因此必须先构造一个包含所有年份和季度的字段来作为辅助表,然后剩下的常规套路就是使用度量值来控制哪些柱子应该展示。
但在该案例中,控制柱子的展示有一种更简单的方法,那就是借助Auto-Exists机制来进行过滤,只需要在辅助表中加一个所属年份的字段即可。
最后,还需要以正确的顺序来显示年份与季度,因此还需要给辅助表再加一个排序字段,以便设置按列排序。
解决方案
首先,使用以下计算表表达式创建一个标准的日期表:
Calendar =
var startdate=DATE(YEAR(MIN('Fact'[Date])),1,1)
var enddate=DATE(YEAR(MAX('Fact'[Date])),12,31)
RETURN
ADDCOLUMNS(
CALENDAR(startdate,enddate),
"Year Number",YEAR([Date]),
"Year",FORMAT([Date],"yyyy"),
"Month Number",MONTH([Date]),
"Month",FORMAT([Date],"mmmm"),
"Quarter Number",ROUNDUP(MONTH([Date])/3,0),
"Quarter","Q"&ROUNDUP(MONTH([Date])/3,0),
"WeekNum",WEEKNUM([Date],2),
"WeekDay Number",WEEKDAY([Date],2),
"WeekDay",SWITCH(WEEKDAY([Date],2),1,"周一",2,"周二",3,"周三",4,"周四" ,5,"周五",6,"周六" ,7,"周日"),
"YearMonth Number",YEAR([Date])*100+MONTH([Date]),
"YearMonth",FORMAT([Date],"yyyy.mm"),
"YearQuarter Number",YEAR([Date])*10+ROUNDUP(MONTH([Date])/3,0),
"YearQuarter",YEAR([Date])&"Q"&ROUNDUP(MONTH([Date])/3,0)
)
然后再使用以下计算表表达式来创建用于柱形图X轴的辅助表:
Calendar_Show_Qtr =
SELECTCOLUMNS(
UNION(
ADDCOLUMNS(ALL('Calendar'[Year Number],'Calendar'[Year]),"Type","Year","OwnerYear",'Calendar'[Year Number]),
ADDCOLUMNS(ALL('Calendar'[YearQuarter Number],'Calendar'[YearQuarter]),"Type","YearQuarter","OwnerYear",LEFT('Calendar'[YearQuarter],4)*1)
),
"Index",IF([Type]="Year",'Calendar'[Year Number],'Calendar'[Year Number]/10),
"Item",'Calendar'[Year],
"Type",[Type],
"OwnerYear",[OwnerYear]
)
该辅助表的表结构如下图所示:
然后将日期表与事实表连接关系,辅助表则不需要连接任何关系,最终的模型关系如下图所示:
然后,创建如下度量值:
度量值 =
VAR CurIndex = MAX('Calendar_Show_Qtr'[Index])
VAR CurType = MAX('Calendar_Show_Qtr'[Type])
RETURN
IF(CurType="Year",
CALCULATE(SUM('Fact'[Value]),'Calendar'[Year Number]=CurIndex),
CALCULATE(SUM('Fact'[Value]),'Calendar'[YearQuarter Number]=CurIndex*10)
)
然后创建一个切片器和一个柱形图,切片器所用字段为辅助表的OwnerYear字段,柱形图的X轴为辅助表的Item字段,最后再将上面的度量值作为柱形图的Y轴即可,如下图所示:
该方案利用了Auto-Exists机制来过滤应展示的柱子,并使用按列排序功能来实现排序,因此度量值中只需要进行筛选器的正确匹配即可。
另外,由于度量值里的条件判断与筛选器转移等均是使用的年份或季度的索引字段,因此前端的年份与季度等字段的展示格式可以随意变化,并不会造成影响。
案例扩展
上面的解决方案中,切片器的年份字段其实也可以直接使用日期表中的年份字段,如下图所示:
但当切片器中使用来自日期表的年份字段时,其中的原理就发生了变化,此时不再是通过Auto-Exists机制来过滤应展示的柱子,而是在度量值中通过筛选器相交使得值为空,并借助柱形图会自动隐藏度量值为空的那些柱子的特性来过滤应展示的柱子。
虽然原理不太一致,但实现的效果是相同的,切片器的年份字段可以使用日期表的也可以使用辅助表的,实际场景中可以根据切片器是否还需要筛选其他视觉对象来灵活决定该使用的字段。
另外,上面展示的是同一年的年份与季度紧挨着放在一起的样式,但有时候可能会想让所有年份在左边,而所有季度在右边,比如下图所示的效果:
想要实现这种效果也很简单,只需要在上面的解决方案的基础上微调一下辅助表的排序字段的逻辑,如下图所示:
最后再对应的更改一下度量值里的季度部分的索引逻辑即可,如下图所示:
总结
以上方法仅供参考,若有更优雅的解决方案,欢迎留言讨论,或者加入我们的技术交流群,一起享受这种思维碰撞的快乐吧!
PBI/DAX技术交流群(QQ):344353627