继续来学习最近新推出的可视化计算,如果你还不熟悉,可以先看看之前已经介绍的几篇文章:
2024开年Power BI重磅更新,先来体验一下可视化计算怎么用?
深入了解Power BI可视化计算(1):RUNNINGSUM运行总和
深入了解Power BI可视化计算(2):MOVINGAVERAGE移动平均
这里我们接着介绍内置模式的父级百分比,以PowerBI星球案例模型制作的这个矩阵为例:
新建可视化计算,模式选择“父级的百分比”,可以看到默认出现的公式是这样的:
这个计算的关键是COLLAPSE函数,所以我们先来掌握这个函数。
COLLAPSE函数
它只能用于可视化计算,有两种功能,对应不同的语法结构。
1、只有轴作为参数时,检索比当前上下文更高级别的上下文,可以用作CALCULATE函数的调节器。
比如返回当前上下文的上一级的销售额,可以这样写:
它可以折叠当前上下文所在的层级,在产品名称这个层级上,它返回所属的产品类别的销售额,而在产品类别层级上,它返回整体的销售额,
由于这个矩阵的层级都位于行标题,所以轴用的是ROWS;如果你的层级位于列上,还可以改成COLUMNS,关于方向轴的具体用法见前面RUNNINGSUM的介绍。
如果要返回上2级的数据,第2参数可以写成2(省略时默认为1,返回上一级):
由于我们这个结构只有两级,这样它都返回了最高层级也就是整体销售额。
除了用方向轴作为参数,它也支持直接用层级的字段作为参数,比如返回产品名称上一级的销售额,可以这样写:
它固定的只折叠产品名称上下文,而不是像ROWS一样折叠当前层级,所以这里的每个产品类别层级的总计还是该类别的合计,与上面用ROWS作为参数的结果是不同的。
2、如果用表达式和轴同时作为参数,则可以直接返回新上下文的值,不再需要CALCULATE。
比如返回上级销售额,或者父级销售额,可以直接这样写:
上级销售额 = COLLAPSE([销售额],ROWS)
它和CALCULATE([销售额],COLLAPSE(ROWS))的效果是一样的。
第二参数同样可以用具体字段代替,比如:
产品类别销售额 = COLLAPSE([销售额],[产品名称])
它和CALCULATE([销售额],COLLAPSE([产品名称]))的效果是一样的。
还有一个类似的函数COLLAPSEALL,这里也顺便提一下,它和COLLAPSE函数一样也有上面两种用法,不过它的轴只能是轴参数ROWS/COLUMNS等,不能是字段,它返回最高层级的结果,计算整体总计非常好用。
利用COLLAPSE计算各种占比
之前介绍过用度量值来计算占比的各种方法,见:利用ALL和ALLSELECTED灵活计算占比
有了上面介绍的COLLAPSE,用可视化计算想要哪个占比,都很容易实现。
占总体的比例
占总体的比例可以这样写:
占总体的比例 = DIVIDE([销售额],COLLAPSE([销售额],[产品类别]))
也可以这样写:
占总体的比例2 =
DIVIDE([销售额],COLLAPSE([销售额],ROWS,2))
还可以直接用COLLAPSEALL函数:
占总体的比例3 =
DIVIDE([销售额],COLLAPSEALL([销售额],ROWS))
上面三种写法结果都是一样的。
占本类别的比例
计算每个产品销售额在当前类别的占比:
占本类别的比例 =
DIVIDE([销售额],COLLAPSE([销售额],[产品名称]))
按层级计算占比
如果想按层级计算占比,怎么做呢?按常规思路,只需要把上面两种情况结合到一起就行了:
层级占比 =
IF(ISINSCOPE([产品名称]),[占本类别的比例],[占总体的比例])
这和我们之前介绍的层级占比思路是一样的,只是这里计算的时候,用的是可视化计算方式。
不过对于层级占比,上面的写法依然有点繁琐,更简单的方式是直接用内置的“父级的百分比”写法,用ROWS作为COLLAPSE的参数:
父级百分比 =
DIVIDE([销售额], COLLAPSE([销售额], ROWS))
它的结果和上面层级占比的结果完全一样,不需要用ISINSCOPE来判断层级。层级占比的内在逻辑本来就是父级百分比,用ROWS作为参数时,COLLAPSE([销售额], ROWS)会自动返回当前层级的上一级的数据,也就是父级的数据。
对于占比,如果想显示成百分比格式,目前还不支持直接修改,可以套个FORMAT函数来实现:
以上就是用可视化计算来求占比的做法,关键是要灵活运用COLLAPSE函数的不同用法。
PowerBI星球