分类:多维数据集
我们先来看一下CUBEMEMBER函数,这个函数的作用是用来返回Cube中的成员。
语法如下:
CUMEMEMBER(连接,成员表达式,[文本])
其中,
连接 => 简单理解为当前Excel中的数据模型。因为一个Excel文件只能有一个数据模型,所以这个名字是固定的:ThisWorkbookDataModel。注意,这是一个文本,必须写在引号里。
成员表达式 => 要返回哪一个成员。写法是这样的:[表].[字段].&[项目]。其中,表指的是数据模型中的表的名称;字段是该表中的列名称;项目是该列中的一个值。例如:[SalesData].[产品].&[芬达]。注意,这个参数也是文本,必须写在引号里。
文本 => 是一个可选的参数。如果不使用该参数,公式返回的是成员在表中的值,如果使用该参数,公式返回的是这个参数指定的值。
实际上,我们在将透视表转换为公式后,那些用来分类的维度也转换为CUBEMEMBER函数了:
提醒关注:
“以下项目的综合:数量”这个成员,是[Measures]组中的一项。
行总计和列总计对应参数的写法。
引用具体字段中的某一项,要写&,写全部的项目[All]时,不用&。
CUBEVALUE函数的作用是根据给定的MEMBER,返回对应的度量值。
语法如下:
CUBEVALUE(连接,[成员表达式1],......)
其中,
连接 => 简单理解为当前Excel中的数据模型。因为一个Excel文件只能有一个数据模型,所以这个名字是固定的:ThisWorkbookDataModel。注意,这是一个文本,必须写在引号里。
成员表达式 => 需要返回的值所属的成员。写法是这样的:[表].[字段].&[项目]。可以省略。
下面举几个例子帮助我们理解CUBEVALUE函数。
例1
=CUBEVALUE("ThisWorkbookDataModel")
CUBEVALUE函数可以除了连接外,没有任何参数。但是,这个公式返回空值。(实际上是返回空文本)
这就好像在透视表中没有设置任何字段,只有一个空的透视表一样。
例2
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[以下项目的总和:数量]")
如果公式中没有指示任何维度,只有一个度量成员的话,显示的是总计。
这就好像在透视表中只有值字段,没有其他行字段和列字段一样。
例3
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[以下项目的总和:数量]","[SalesData].[月份].&[一月]")
这就是CUBEVALUE函数的标准用法。相当于透视表中既有值字段,也有行字段,或者列字段。
注:在CUBEVALUE函数中,这些成员之间的先后顺序没有任何关系,可以将度量写在前面,也可以将其他成员写在前面。
例4
=CUBEVALUE("ThisWorkbookDataModel","[SalesData].[月份].&[一月]")
CUBEVALUE函数中可以不写任何度量值成员,只写其他维度成员。但是,返回的结果永远为1。
例5
=CUBEVALUE("ThisWorkbookDataModel",$B$3,$B6,D$4)
在CUBEVALUE函数中,成员可以通过单元格引用指定。但是,你不能简单的在单元格中输入“一月”,“芬达”等文本,然后就在CUBEVALUE公式中引用,那样会返回#N/A的错误值。只有单元格中是用CUBEMEMBER函数返回的结果时,才能在CUBEVALUE中引用该单元格。
实际上,在这个例子的公式中,CUBEVALUE并不是使用那些单元格的显示值,而是使用该单元格的公式中所指明的成员表达式。
小插曲
这里有一个"bug"。如果你在一个单元格(比如B1)中使用CUBEMEMBER函数返回了一个成员,然后在CUBEVALUE函数中引用B1单元格,这当然会返回正确结果。现在,你将B1单元格复制,然后粘贴成数值,你会发现CUBEVALUE依然返回正确的结果。只有当你手动输入该值的时候才会出错!
度量值
在上面的例子中,我们查询的度量值是:
以下项目的总和:数量
这是中文版Excel中固定的写法。类似的还有计数,下图展示了一些常见的写法:
类似于“以下项目的计数”这样的度量值称为隐式度量值,实际上是对表格中字段的直接汇总。
如果不知道怎么写,也可以创建一个透视表,看看名字是什么
你也可以自己创建度量值。像上图中的占比就是我自己创建的一个度量。关于如何创建度量值,请参见Power BI相关文章:
或学习我们相关的视频课程。
我们可以添加针对数据模型的切片器。
在Excel中,点击插入选项卡,点击切片器:
然后选择“数据模型”,
选择需要添加的字段:
添加一个切片器,
在切片器上点击右键,在菜单中点击切片器设置,打开切片器设置对话框,
记住红色加亮的切片器名称:切片器_月份,
在CUBEVALUE公式中将该名称添加为一个参数:
现在,CUBEVALUE已经可以通过切片器改变不同月份的汇总结果了。
实际上,这里切片器被当作CUBE的成员使用了。
注:当使用切片器作为成员时,注意不要放在引号里。
未完待续
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
联系客服