Excel Power Pivot俗称超级透视表,具有强大的建模能力。一般情况下,Power Pivot的模型在Excel界面以数据透视表或数据透视图展现。但是,这种展现方式比较单一,无法实现复杂结构报表提取模型数据的需求。
CUBEVALUE提取模型数据生成复杂格式报表
在将Power Pivot模型数据取值到单元格中这篇文章中,我介绍了如何使用CUBEVALUE函数在单元格中输出Power Pivot模型生成的结果,实现建模强大与表达灵活并存的效果。本文更进一步,介绍CUBEVALUE函数的更多用法。样例数据依然是这个销售达成模型:
1.CUBEVALUE+切片器
透视表可以增加切片器,切换数据范围,CUBEVALUE函数将Power Pivot数据取值到单元格中后,是否同样可以切片?
答案是可以的。在公式的末端,只要输入“切片器”,就会弹出报表中所有的切片器,选择其中一个,切片器即可与复杂格式的报表联动。
=CUBEVALUE("ThisWorkbookDataModel","[销售员].[销售员].["&B5&"]","[Measures].[M_销售额]",切片器_星期)
如下为切片动态效果:
和透视表一样,同一个CUBEVALUE公式可以并列多个切片器对数据发生作用,下方示例为两个切片器:
=CUBEVALUE("ThisWorkbookDataModel","[销售员].[销售员].["&B52&"]","[Measures].[M_销售额]",切片器_工龄,切片器_职级)
动画可以看到,一个切片器可以同时控制透视表和CUBE生成的表格:
2.CUBEVALUE+CUBEMEMBER
CUBE函数是个大家族,其中CUBEVALUE和CUBEMEMBER配合可以实现对模型中的度量值自由切换。复杂格式报表的精髓在于
常规的办法是变更E列的每一个文字,并且变更F列的每一个公式。有了CUBEMEMBER大可不必如此麻烦。
首先,E列全部变更为CUBEMEMBER而不是普通文字,但是看上去和普通文字没有什么区别:
E6=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[M_客单量]","客单量")
E7=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[M_客单价]","客单价")
E8=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[M_件单价]","件单价")
E9=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[M_销售笔数]","销售笔数")
F列的CUBEVALUE函数也相应变更,为了方便对照,将变更前后的公式同时列示:
变更前=CUBEVALUE("ThisWorkbookDataModel","[销售员].[销售员].["&$B$5&"]","[Measures].[M_销售笔数]",切片器_星期)
变更后=CUBEVALUE("ThisWorkbookDataModel","[销售员].[销售员].["&$B$5&"]",E6,切片器_星期)
可以看到F列的区别是,变更前CUBEVALUE引用的是DAX建立的度量值,而变更后引用的是E列的单元格值。在这样的结构下,E列的指标名称任意变化,F列的数据会对应变化为该指标数据:
CUBEMEMBER函数最少两个参数,可以有三个参数,三个参数本例描述为:
CUBEMEMBER(这个模型,取哪个度量值,给这个度量值取个别名)
本例Power Pivot后台DAX新建的度量值都带有M字样,直接体现在报表中不雅观,因此推荐启用第三个参数。
3.总结
本文结合上一篇CUBEVALUE的介绍文章,可以给我们一个启示:DAX的建模能力犹如深厚的内功,你的内力如果只是通过透视表这一个招式表达出来未免太过无趣(Power BI用户请飘过),而CUBE函数给了你表达的自由。
本系列预计还有后续。