感谢杨召军老师的提示,看了他的图,我才想到怎么一点一点去实现。
准备数据
将现金流量明细数据、分类维度表导入PBI,如下图:
为明细项目和分类指定好排序依据的列。
再加入一个堆积辅助的表,作用会在后面讲解。
这些表的关系图如下:
建立度量值
先建两个基础度量值:
现金流金额 = SUM('现金流量'[金额])
现金流净额 = SUM('现金流量'[净额])
接下来就是我们的两个关键的度量值
我们先看我们最终想要的图形。
我们是要将所有现金流的项目分为了经营活动、投资活动、筹资活动三大类,然后在大类的内部进行累加。我们需要一个度量来实现这种在大类之内进行累加的计算。一个度量值计累加到当前的值,另一个度量值计算累加到前一项的值,以此为确定当前所要的柱形的起点与终点。这样才能实现用堆积柱状图作出瀑布图的效果。
在大类内累加至当前的度量值:
现金流量堆积.cum0 =
var cat=SELECTEDVALUE('流量分类'[分类])
var tb1=CALCULATETABLE(
SUMMARIZE('流量分类','流量分类'[明细项目],'流量分类'[明细序号]),
ALLSELECTED('流量分类'[明细项目]),
ALL('流量分类'),
'流量分类'[分类]=cat
)
VAR vIndex=ROWNUMBER(
tb1,
ORDERBY('流量分类'[明细序号],ASC)
)
var tb=
ADDCOLUMNS(
tb1,
"@Value",[现金流净额]
)
var tb2=WINDOW(-vIndex,REL,0,REL,tb,ORDERBY('流量分类'[明细序号],ASC))
var cum=SUMX(tb2,[@Value])
return cum
在大类内累加至前一项的度量值:
现金流量堆积.cum-1 =
var cat=SELECTEDVALUE('流量分类'[分类])
var tb1=CALCULATETABLE(
SUMMARIZE('流量分类','流量分类'[明细项目],'流量分类'[明细序号]),
ALLSELECTED('流量分类'[明细项目]),
ALL('流量分类'),
'流量分类'[分类]=cat
)
VAR vIndex=ROWNUMBER(
tb1,
ORDERBY('流量分类'[明细序号],ASC)
)
var tb=
ADDCOLUMNS(
tb1,
"@Value",[现金流净额]
)
var tb2=WINDOW(-vIndex,REL,-1,REL,tb,ORDERBY('流量分类'[明细序号],ASC))
var cum=SUMX(tb2,[@Value])
return cum
分情况建立不同的度量值
观查我们所要的图形,会发现同一项内,我们只显示一条柱子是不够的。比如,图中圈出来这部分,是一个现金支出,并使得累计值从正数变成了负数。在这样的情况下,使其它柱子高度归为0,只留一个柱子的高度是不行的,必须要有2个柱子有高度,一个是正数,一个负数,才能实现图中的效果。
为此,我们可以将我们所要的柱子分成5种情况,在0线上方的上升部分,在上方的下降部分,在下方的上升部分,在下方的下降部分,和留出空白的部分。
我们分别编写度量值如下:
现金流量堆积.正数.上方 = if( [现金流量堆积.cum0]>0&&[现金流净额]>0, MIN([现金流净额],[现金流量堆积.cum0]))
现金流量堆积.负数.上方 = if( [现金流量堆积.cum-1]>0&&[现金流净额]<0, MIN(ABS([现金流净额]),[现金流量堆积.cum-1]))
现金流量堆积.正数.下方 = if( [现金流量堆积.cum-1]<0&&[现金流净额]>0, -MIN([现金流净额],ABS([现金流量堆积.cum-1])))
现金流量堆积.负数.下方 = if( [现金流量堆积.cum0]<0&&[现金流净额]<0, -MIN(ABS([现金流净额]),ABS([现金流量堆积.cum0])))
还有一个留出空白部分的度量值:
现金流量堆积.空白 =
var CumPre=[现金流量堆积.cum-1]
var CumCur=[现金流量堆积.cum0]
var rst1=SWITCH(
TRUE,
CumPre>=0&&CumCur>=0,MIN(CumPre,CumCur),
CumPre<=0&&CumCur<=0,MAX(CumPre,CumCur),
CumPre>=0&&CumCur<=0,0,
CumPre<=0&&CumCur>=0,0
)
var rst=if([现金流净额],rst1)
return rst
汇总为一个度量值
度量值如下:
现金流量.瀑布 =
var L=SELECTEDVALUE('流量分类'[层次])
var L2=
SWITCH(
SELECTEDVALUE('堆积辅助'[堆积辅助列]),
1,[现金流量堆积.正数.上方],
2,[现金流量堆积.负数.上方],
3,[现金流量堆积.负数.下方],
4,[现金流量堆积.正数.下方],
5,[现金流量堆积.空白]
)
var L1=CALCULATE([现金流净额],ALL('流量分类'),'流量分类'[分类]=SELECTEDVALUE('流量分类'[分类]))
return SWITCH(
TRUE(),
L=2,L2,
L=1&& L1>=0 &&SELECTEDVALUE('堆积辅助'[堆积辅助列])=6,L1,
L=1&& L1<=0 &&SELECTEDVALUE('堆积辅助'[堆积辅助列])=7,L1
)
这样做的目标,是因为这样的话,我们就可以将堆积辅助列放到图例栏中,用针对图例的配色去控制各部分的颜色。并且,这样做之后,还可以利用组合图,在堆积柱状图中再加入一个虚线的折线图,来描述出流量的起点、终点和具体金额。
并且,我们的图中是有类似小计的效果的,也就是将三大类现金流量的净额,用特殊颜色的柱子进行了强调。如下图。上面的度量值,也能很好的处理这一点。
再为折线图编写一个度量值,用于标记流量金额
现金流量.瀑布.线 =
IF(
SELECTEDVALUE('流量分类'[层次])=2,
IF(
[现金流净额]>0,
IF(
[现金流量堆积.cum0]>0,
[现金流量堆积.正数.上方]+[现金流量堆积.空白],
[现金流量堆积.空白]
),
IF(
[现金流量堆积.cum0]<0,
[现金流量堆积.负数.下方]+[现金流量堆积.空白],
[现金流量堆积.空白]
)
),
CALCULATE([现金流净额],ALL('流量分类'),'流量分类'[分类]=SELECTEDVALUE('流量分类'[分类]))
)
建立可视化图形
我们选择这个折线和堆积柱形图
为各栏配置度量值如下图:
于是,我们得到了这样一个图形:
当然,这个还不是我们最终想要的。我们还需要为各部分进行配色,最重要的是为空白的部分配上白色或其他与底色相同的颜色,以隐藏该部分。
根据图例配置颜色如下:
这样便得到了我们最终想要的图形。
示例文件下载
链接:百度网盘