计算组是PowerBI刚更新不久的功能,关于计算组的介绍可以看SQLBI的系列文章 Introducing Calculation Groups - SQLBI
计算组依赖于外部工具:Tabular Editor
在这里分享一个具体的应用,在这个例子里面,可以自由切换KPI以及切换不同的时间智能,并且支持日期的层级下钻:
这里用到的图表是折线和簇状柱形图,支持列值,列序列,行值三个维度和指标的组合,所以这个例子会分三步实现:(1)列值自由切换KPI;(2)列序列自由选择当期值,上期值,去年值以及YTD;(3)行值自由切换同比或者环比。
(1)列值自由切换KPI
1.创建一个计算组CG-KPI,计算组中的每个计算项就是对应的度量值,比如销售金额这个计算项的表达式直接就是 [销售金额] 这个度量值。
2.创建一个度量值来引用计算组
指标(综合) = CALCULATE([单价],'CG-KPI'[KPI])
3.把上一步创建的度量值拉到列值,再把计算组拉到切片器,当选择了CG-KPI这个计算组中的某个计算项,这个计算项会覆盖前面的[单价]度量值,就能展示该计算项对应的KPI。所以前面的度量值是可以随便选择的,因为最终都会被覆盖。
(2)列序列自由选择当期值,上期值,去年值以及YTD
1.创建一个计算组CG-Time intelligence,对应的计算项如下:
1当期值:SELECTEDMEASURE ()
2上期值Last Period:
VAR P =SELECTEDMEASURE ()
var period1 = FIRSTDATE('Date'[Date])
var period2 =LASTDATE('Date'[Date])
var daydiff = DATEDIFF(period1,period2,day)
var result =SWITCH(true(),daydiff>300,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,YEAR)),
daydiff>80,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,QUARTER)),
daydiff>20,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,MONTH)),
daydiff=0,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,DAY)))
return result
3去年值Last Year:
VAR result = CALCULATE ( SELECTEDMEASURE (),
SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
return result
4YTD年初至今:
VAR result = CALCULATE (
SELECTEDMEASURE (),
DATESYTD ( 'Date'[Date] )
)
return result
每个计算项就是对应的时间智能,SELECTEDMEASURE()是度量值占位符。
2.把CG-Time intelligence这个计算组拉到列序列,第一步中选择的计算项会替代SELECTEDMEASURE(),也就实现了可以计算不同KPI的对应时间智能。
再把CG-Time intelligence这个计算组拉到切片器,实现列序列的自由选择。
(3)行值自由切换同比或者环比
1.创建另外一个计算组CG-Time intelligence1,计算项为环比上期、同比去年,公式如下:
1环比上期:
VAR P =SELECTEDMEASURE ()
var period1 = FIRSTDATE('Date'[Date])
var period2 =LASTDATE('Date'[Date])
var daydiff = DATEDIFF(period1,period2,day)
var result =SWITCH(true(),daydiff>300,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,YEAR)),
daydiff>80,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,QUARTER)),
daydiff>20,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,MONTH)),
daydiff=0,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,DAY)))
return
DIVIDE (P-result,result)
2同比去年:
VAR CurrYear =SELECTEDMEASURE ()
VAR PrevYear =CALCULATE (
SELECTEDMEASURE (),
SAMEPERIODLASTYEAR ( 'Date'[Date] ))
return
DIVIDE (CurrYear - PrevYear,PrevYear )
2.创建一个度量值来引用计算组
同环比(综合) = CALCULATE([CG-Time intelligence1],'CG-KPI'[Time intelligence1])
3.把上一步创建的度量值拉到行值,会发现列值也就是KPI被替换成同环比的值,这是因为又发生了一次计算项的覆盖,CG-Time intelligence1里面的计算项会把第一步 [指标(综合)]这个度量值覆盖。
因此修改计算项的公式,引入NOT ISSELECTEDMEASURE 这个语法让 [指标(综合)] 这个度量值不被覆盖:
1环比上期:
VAR P =SELECTEDMEASURE ()
var period1 = FIRSTDATE('Date'[Date])
var period2 =LASTDATE('Date'[Date])
var daydiff = DATEDIFF(period1,period2,day)
var result =SWITCH(true(),daydiff>300,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,YEAR)),
daydiff>80,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,QUARTER)),
daydiff>20,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,MONTH)),
daydiff=0,CALCULATE(SELECTEDMEASURE (),
dateadd('Date'[Date],-1,DAY)))
return
IF(
NOT ISSELECTEDMEASURE([指标(综合)]),
DIVIDE (P-resurt,resurt),[指标(综合)])
2同比去年:
VAR CurrYear =SELECTEDMEASURE ()
VAR PrevYear =CALCULATE (
SELECTEDMEASURE (),
SAMEPERIODLASTYEAR ( 'Date'[Date] ))
return
IF(
NOT ISSELECTEDMEASURE([指标(综合)]),
DIVIDE (CurrYear - PrevYear,PrevYear ),[指标(综合)])
通过以上三个步骤就可以实现文章开头实现的效果,自由切换KPI,自由选择当期值,上期值,去年值以及YTD,自由切换同比或者环比。