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

2019-08-29 12:12:43 ⋅ 2649 ⋅ 0 ⋅ 0


小伙伴们大家好！
☞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工坊

自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）