DAX 综合实战案例—简单的中国式报表详解(三)


小伙伴们大家好!
☞DAX综合实战案例-简单的中国式报表详解(一)
☞DAX综合实战案例-简单的中国式报表详解(二)
中讲解了中国式表头的构建,按列排序,时间智能函数,这些菜都已经做好,最后一步就是拼盘了,如何把它们按照需要的样子拼在一起呢?
接下来就处理这个问题。


我们再来看下报表的样子:
file

file

报表先是展示各月的预算达成情况,然后是年累计、上年全年、上年年累计情况,最后是差异比较。表头通过按列排序已经实现了,现在就是要把对应的度量也体现在对应的表头下面。
还是要用到DAX公式来实现。报表的行列筛选是一种外部筛选,如何通过DAX控制来实现这一效果呢?可以考虑用SWITCH+SELECTEDVALUE的方式来实现。
比如,各月的预算、达成、达成率度量可以这么写:

 LMeasure=
SWITCH(TRUE(),
//各月度预算、达成、达成率
SELECTEDVALUE('表头'[L1])="月份"&&SELECTEDVALUE('表头'[L3])="预算",
CALCULATE('事实表'[预算额],TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY())),
SELECTEDVALUE('表头'[L1])="月份"&&SELECTEDVALUE('表头'[L3])="达成",
CALCULATE('事实表'[达成额],TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY())),
SELECTEDVALUE('表头'[L1])="月份"&&SELECTEDVALUE('表头'[L3])="达成率",
CALCULATE('事实表'[达成率],TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY()))

通过SELECTEDVALUE获得表头L1字段和表头L3字段的筛选(以上筛选的是月份及对应的预算达成,这是外部筛选),TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY())这是公式筛选,把当前年月的度量计算出来,通过TREATAS使得表头L2与日期表的月份建立虚拟关系,而日期表又与事实表存在物理关系,通过表关联传递筛选关系,进而计算出对应筛选条件下的度量。
同样的道理,今年年累计的度量如下:

 LMeasure0 = 
SWITCH(TRUE(),
//今年累计预算、达成、达成率
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="今年全年",
'事实表'[预算_YTD2],
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="今年全年",
'事实表'[达成_YTD],
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="今年全年",
'事实表'[达成率_YTD])

上年全年、上年年累计和差异对比都是一样的套路,完整的公式如下:

 LMeasure0 = 
SWITCH(TRUE(),
//各月度预算、达成、达成率
SELECTEDVALUE('表头'[L1])="月份"&&SELECTEDVALUE('表头'[L3])="预算",
CALCULATE('事实表'[预算额],TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY())),
SELECTEDVALUE('表头'[L1])="月份"&&SELECTEDVALUE('表头'[L3])="达成",
CALCULATE('事实表'[达成额],TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY())),
SELECTEDVALUE('表头'[L1])="月份"&&SELECTEDVALUE('表头'[L3])="达成率",
CALCULATE('事实表'[达成率],TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY())),
//今年累计预算、达成、达成率
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="今年全年",
'事实表'[预算_YTD2],
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="今年全年",
'事实表'[达成_YTD],
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="今年全年",
'事实表'[达成率_YTD],
//上年全年预算、达成、达成率
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="上年全年",
'事实表'[预算_PY],
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="上年全年",
'事实表'[达成_PY],
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="上年全年",
'事实表'[达成率_PY],
//上年累计预算、达成、达成率
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="上年年累计",
'事实表'[预算_YTD_PY2],
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="上年年累计",
'事实表'[达成_YTD_PY],
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="上年年累计",
'事实表'[达成率_YTD_PY],
//今年与上年预算、达成、达成率差异
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="CY-PY",
'事实表'[预算_YTD2]-'事实表'[预算_PY],
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="CY-PY",
'事实表'[达成_YTD]-'事实表'[达成_PY],
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="CY-PY",
'事实表'[达成率_YTD]-'事实表'[达成率_PY],
//今年与上年累计预算、达成、达成率差异
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="CY-PY_YTD",
'事实表'[预算_YTD2]-'事实表'[预算_YTD_PY2],
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="CY-PY_YTD",
'事实表'[达成_YTD]-'事实表'[达成_YTD_PY],
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="CY-PY_YTD",
'事实表'[达成率_YTD]-'事实表'[达成率_YTD_PY]
)

我数了一下有45行,是有点儿长。
貌似大功告成了,在画布上看下效果吧:

file

file

我先拖表头的话,会报错的,单看表头和事实表的字段城市之间是没有什么关系的,它们在这里都是维度表,两个维度之间是没有什么关系的,它们只与事实表是有关系的,所以这时候先把城市从行上去掉,把LMeasure0拖到值字段上,列的层级下钻到L3级别,这时就出来效果了,这时候再把城市字段拖到到行字段处是OK的。
file

行列两个维度对事实表进行筛选是没有问题的。

file

还需要注意的小问题是小计中的列小计要关闭掉,不然差异那块是空白。

file

关闭后的效果

file

file

file

初步看的话,效果基本上是OK的,基本算是实现了,但是仔细看的话,你会发现,数字的格式是有问题的,预算、达成希望只留千分位,不留小数位,达成率用百分比的形式显示。
如果是单个度量值,我们可以直接在格式选项卡里设置,但是这是多个度量值公式筛选的结果,无法直接在选项卡里设置,那怎么办?
还是要用DAX公式来设置,它就是FORMAT函数。

file

参考格式参数,对公式进行下格式处理。

 LMeasure = 
SWITCH(TRUE(),
//各月度预算、达成、达成率
SELECTEDVALUE('表头'[L1])="月份"&&SELECTEDVALUE('表头'[L3])="预算",
FORMAT(CALCULATE('事实表'[预算额],TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY())),"#,##"),
SELECTEDVALUE('表头'[L1])="月份"&&SELECTEDVALUE('表头'[L3])="达成",
FORMAT(CALCULATE('事实表'[达成额],TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY())),"#,##"),
SELECTEDVALUE('表头'[L1])="月份"&&SELECTEDVALUE('表头'[L3])="达成率",
FORMAT(CALCULATE('事实表'[达成率],TREATAS(VALUES('表头'[L2]),'日期表'[月份]),YEAR('事实表'[Date])=YEAR(TODAY())),"0%"),
//今年累计预算、达成、达成率
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="今年全年",
FORMAT('事实表'[预算_YTD2],"#,##"),
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="今年全年",
FORMAT('事实表'[达成_YTD],"#,##"),
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="今年全年",
FORMAT('事实表'[达成率_YTD],"0%"),
//上年全年预算、达成、达成率
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="上年全年",
FORMAT('事实表'[预算_PY],"#,##"),
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="上年全年",
FORMAT('事实表'[达成_PY],"#,##"),
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="上年全年",
FORMAT('事实表'[达成率_PY],"0%"),
//上年累计预算、达成、达成率
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="上年年累计",
FORMAT('事实表'[预算_YTD_PY2],"#,##"),
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="上年年累计",
FORMAT('事实表'[达成_YTD_PY],"#,##"),
SELECTEDVALUE('表头'[L1])="总计"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="上年年累计",
FORMAT('事实表'[达成率_YTD_PY],"0%"),
//今年与上年预算、达成、达成率差异
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="CY-PY",
FORMAT('事实表'[预算_YTD2]-'事实表'[预算_PY],"#,##"),
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="CY-PY",
FORMAT('事实表'[达成_YTD]-'事实表'[达成_PY],"#,##"),
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="CY-PY",
FORMAT('事实表'[达成率_YTD]-'事实表'[达成率_PY],"0%"),
//今年与上年累计预算、达成、达成率差异
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="预算"&&SELECTEDVALUE('表头'[L2])="CY-PY_YTD",
FORMAT('事实表'[预算_YTD2]-'事实表'[预算_YTD_PY2],"#,##"),
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="达成"&&SELECTEDVALUE('表头'[L2])="CY-PY_YTD",FORMAT('事实表'[达成_YTD]-'事实表'[达成_YTD_PY],"#,##"),
SELECTEDVALUE('表头'[L1])="差异"&&SELECTEDVALUE('表头'[L3])="达成率"&&SELECTEDVALUE('表头'[L2])="CY-PY_YTD",
FORMAT('事实表'[达成率_YTD]-'事实表'[达成率_YTD_PY],"0%")
)

再来看下效果
file

file

file

格式是变了,但又出现了新的问题,未发生月的也显示出来了,如何只显示发生月的呢?我试了下用DAX度量进行干预,但是没奏效,大家有什么好的建议欢迎留言告知。用POWER BI DESKTOP的外部筛选去掉即可。
file

file

最后调整下图表配色。

file

这样,简单的中国式报表就制作完成了。下次再见了~~

file

  • PowerPivot工坊原创文章,转载请注明出处!

    如果您想深入学习微软Power BI,欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号(PowerPivot工坊)后猛戳”在线学习”。


长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯,欢迎小伙伴儿们转发分享~

Power Pivot工坊