PowerBI/DAX的计算组功能是什么？怎么用？

---

一、前言

  在之前介绍PowerBI Desktop的外部工具按钮为灰色而无法使用的解决方法的文章中，因为提及了计算组功能，所以昨天有朋友问我：计算组是什么功能，值得为了它而专门去下载外部工具吗？

  我的回答是值得的，因为计算组功能是很强大的，它主要有两个作用，一个是可自动识别当前度量值并重塑计算逻辑，另一个则是支持动态数据格式。我觉得最重要的还是动态数据格式，因为重塑计算逻辑可以使用新建参数表的方法来达到，但是新建参数表的方法只能有一个统一的格式而不能根据计值环境指定不同的数据格式，因此计算组功能我觉得是不可或缺的，在某些业务情形下非常有用。

二、计算组与SELECTEDMEASURE

2.1 同比数据的度量值实现

  在介绍计算组之前，先来看一个不使用计算组的例子，然后与下面使用计算组的方式做对比，以此来说明计算组的功能。

  来看一个在平时的业务中非常常见的例子，那就是求销售金额、销售数量、利润率等等指标的同比或环比数据以及增长率。例如：我要求当前时期的销售金额以及上年同期的销售金额，并求同比增长率。这非常简单，采用下面给出的度量值公式即可：

CY_Sales = SUMX('T3销售','T3销售'[T3销售册数]*'T3销售'[T3销售单价])

PY_Sales = CALCULATE([CY_Sales],SAMEPERIODLASTYEAR('日期表'[Date]))

YOY%_Sales = DIVIDE([CY_Sales]-[PY_Sales],[PY_Sales])

  其中，CY_Sales是当前时期的销售金额，PY_Sales是上年同期的销售金额，YOY%_Sales 是同比增长率，将这三个度量值放入数据透视表的值字段中，并将年份和月份放入行字段中，得到结果如下图：

  上面求的是销售金额的同比数据，然后，你可能还想知道销售数量的同比数据，这也非常简单，再新建三个度量值，并拖放到值字段中就可以了，度量值公式如下：

CY_Volume = SUM('T3销售'[T3销售册数])

PY_Volume = CALCULATE([CY_Volume],SAMEPERIODLASTYEAR('日期表'[Date]))

YOY%_Volume = DIVIDE([CY_Volume]-[PY_Volume],[PY_Volume])

结果如下图：

  假如，你还想知道利润、成本等等的指标的同比数据，你难道还是继续这样新建度量值吗？虽然是可以得到结果，但是想想：一个指标就需要三个度量值，当指标数量比较多的时候需要多少个度量值？这数量可能会让你头皮发麻，而且这样的重复性工作根本不能体现出写代码的本质：解放双手。

  因此，当你需要建立较多的度量值且这些度量值的计算逻辑一致，那么你可以采用计算组的方法来解放你的双手！

2.2 同比数据的计算组实现一

2.2.1 计算组功能在哪里？

  要想在PowerBI Desktop中使用计算组功能，首先要把PowerBI Desktop更新至2020年七月中旬后发布的版本，然后下载安装外部工具Tabular Editor，并勾选：【文件>选项>预览功能>使用增强的元数据格式存储数据集】选项。

  然后打开外部工具Tabular Editor，若设置正确的话，Tabular Editor会自动读取当前PowerBI中的DAX数据集，如果没有自动读取可以自己手动连接。然后在左边找到Tables文件夹，右键单击，选择新建计算组选项就可以了，如下图：

新建的计算组的层级如下：

>New Calculation Group           
	>calculation item	  
		New Calculation	
		New Calculation 1	
	Name

  计算组设置完成后在PowerBI Desktop中显示的就是一张表，共有两列，表名就是New Calculation Group，第一列的列名就是Name，第二列是自动添加的索引列，可以用来对度量值选项排序。其中，第一列的列值就是计算组层级结构中的calculation item下面的New Calculation，也就是在计算组里的度量值，而计算组的表有几行取决于你在计算组中新建了几个度量值。当然，New Calculation Group、Name、New Calculation这些都是可以自定义名称的。

2.2.2 采用计算组快速得到同比数据

  首先，新建一个计算组，表名命名为：同比的计算组，列名命名为：同比，然后新建三个度量值，分别是：CY、PY以及YOY%，度量值公式如下：

CY = SELECTEDMEASURE()

PY = CALCULATE(SELECTEDMEASURE(),SAMEPERIODLASTYEAR('日期表'[Date]))

YOY% = DIVIDE (
    SELECTEDMEASURE ()
       	- CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( '日期表'[Date] ) ),
    CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( '日期表'[Date] ) )
)

  上面所用的SELECTEDMEASURE函数可以自动识别并获取数据透视表值字段里所用的度量值，其中，CY、PY、YOY%分别为：某个指标的当前时期值、上年同期值与同比增长率，它们在计算组中的表达式定义与格式定义如下图所示：

  虽然Tabular Editor目前版本的中文显示有问题，但是不影响使用，直接将上面给出的公式复制过去，然后设置一下各自的格式就可以了，设置好后如上图所示，点击保存按钮返回PowerBI Desktop。

  回到PowerBI Desktop后，需要点击立即刷新按钮刷新，如上图所示。

  然后可以在右边字段列表里看到刚刚新建的计算组：”同比的计算组“，将其下面的列：“同比“，拖放到数据透视表的列字段，将当前时期的销售金额与销售数量度量值，即：CY_Sales与CY_Volume，拖放到数据透视表的值字段。为了不引起歧义，在这里，将CY_Sales与CY_Volume的名字更改为Sales与Volume。结果如下图：

  如果你需要查看更多指标的同比数据，只需要将其拖放到值字段中即可，不用重复性编写度量值。

2.3 同比数据的计算组实现二

  然后，可能会有人觉得：虽然使用计算组的方法比较简便，但是像上面结果的格式不是我想要的，我不想要CY、PY、YOY%在列的上层，如下图：

我想要的是这种格式的：

  那么，针对这个问题，其实解决方法也很简单，只需要新建一个参数表即可。具体操作如下：

1、 新建一个参数表，就叫：指标参数表，所用DAX公式如下：

指标参数表 = 
SELECTCOLUMNS(
    {("Sales","销售金额",1,"NUM"),
     ("Volume","销售数量",2,"NUM")
    },
    "指标编码",[Value1],
    "指标名称",[Value2],
    "索引排序",[Value3],
    "格式",[Value4]
)

2、新建一个能够根据指标参数计算不同指标数据的度量值，所用DAX公式如下：

计算对应数据的度量值 = 
SWITCH(SELECTEDVALUE('指标参数表'[指标编码]),
    "Sales",[Sales],
    "Volume",[Volume]
)

3、将指标参数表中的指标编码拖放到列字段中，并将之前定义的计算组：”同比的计算组“下面的列：“同比“，拖放到数据透视表的列字段，并置于指标编码的下方，然后将刚刚定义的[计算对应数据的度量值]拖放到数据透视表的值字段，即可得到如下图所示的结果：

三、计算组的动态数据格式

  计算组的另一个强大的功能就是能够动态的设置度量值的数据格式，这在一些特定的业务情形下具有非常重要的作用，能够大大减少工作量。例如：与前面的例子一致，还是求销售金额等指标的同比数据。

  但是这次我还想看看销售金额的同比增长率：YOY％＿Salse的CY、PY以及YOY%的值，而且这次对CY、PY与YOY%的格式有一定的要求，具体如下：

1. 当指标为销售金额和销售数量时，CY、PY的数据格式为常规数值，YOY%的数据格式为百分比。
2. 当指标为销售金额的同比增长率时，CY、PY的格式为百分比，YOY%不再计算增长率，改为计算同比增长值，格式为百分点。

要得到的结果如下图：

实现方法也是比较简单的，具体如下：

1、在上面定义的指标参数表里增加销售金额的同比增长率YOY%_Salse字段，所用DAX公式如下：

指标参数表 = 
SELECTCOLUMNS(
    {("Sales","销售金额",1,"NUM"),
     ("Volume","销售数量",2,"NUM"),
     ("YOY_Salse","销售金额的同比增长率",3,"PCT")
    },
    "指标编码",[Value1],
    "指标名称",[Value2],
    "索引排序",[Value3],
    "格式",[Value4]
)

2、在上面定义的度量值：[计算对应数据的度量值]中增加对应计算分支，所用DAX公式如下：

计算对应数据的度量值 = 
SWITCH(SELECTEDVALUE('指标参数表'[指标编码]),
    "Sales",[Sales],
    "Volume",[Volume],
    "YOY_Salse",[YOY%_Sales]
)

3、打开外部工具：Tabular Editor，展开之前定义的计算组：同比的计算组，修改其中的CY、PY、YOY%的表达式和格式，修改的表达式如下：

CY = SELECTEDMEASURE()

PY = CALCULATE(SELECTEDMEASURE(),SAMEPERIODLASTYEAR('日期表'[Date]))

YOY% =
VAR S =
    CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( '日期表'[Date] ) )
RETURN
    IF (
        SELECTEDVALUE ( '指标参数表'[格式] ) = "PCT",
         ( SELECTEDMEASURE () - S ) * 100,
        DIVIDE ( SELECTEDMEASURE () - S, S )
    )

修改的格式表达式如下：

CY = IF(SELECTEDVALUE('指标参数表'[格式])="PCT","0.00%","#,##")

PY = IF(SELECTEDVALUE('指标参数表'[格式])="PCT","0.00%","#,##")

YOY% = IF(SELECTEDVALUE('指标参数表'[格式])="PCT","0.00","0.00%")

具体如下图所示：

修改完成后保存返回PowerBI Desktop刷新即可得到下图的结果：

四、总结

  关于计算组的两个功能的使用说明就到此为止了，相信你阅读完上面的内容后肯定能有所收获。

  然后需要注意的是，在上面的数据模型中需要使用日期表，因为使用了时间智能函数，需要连续且完整的日期。我上面所使用的数据模型如下：

  下面的是我所使用的日期表的DAX表达式，有需要的可以拿去使用：

日期表 = 
var startdate=DATE(YEAR(MIN('T3销售'[T3销售日期])),1,1)
var enddate=DATE(YEAR(MAX('T3销售'[T3销售日期])),12,31)
return
ADDCOLUMNS(
CALENDAR(startdate,enddate),
"Year Number",YEAR([Date]),
"Year",FORMAT([Date],"yyyy"),
"Month Number",MONTH([Date]),
"Month",FORMAT([Date],"mmmm"),
"Quarter Number",ROUNDUP(MONTH([Date])/3,0),
"Quarter","Q"&ROUNDUP(MONTH([Date])/3,0),
"WeekNum",WEEKNUM([Date],2),
"WeekDay Number",WEEKDAY([Date],2),
"WeekDay",SWITCH(WEEKDAY([Date],2),1,"周一",2,"周二",3,"周三",4,"周四" ,5,"周五",6,"周六" ,7,"周日"), 
"YearMonth Number",YEAR([Date])*100+MONTH([Date]),
"YearMonth",FORMAT([Date],"yy-mmmm"),
"YearQuarter Number",YEAR([Date])*10+ROUNDUP(MONTH([Date])/3,0),
"YearQuarter",YEAR([Date])&"Q"&ROUNDUP(MONTH([Date])/3,0)
)

  最后，如果你没有相关数据进行练习或者想要我上面使用的数据，可以扫描下方二维码关注我的公众号，回复：DAX案例数据，即可获取。

---

补充说明

  本文漏掉了计算组之间的优先级顺序问题，于是在另外一篇文章里介绍了，算是本文的续篇，传送门：PowerBI/DAX的计算组之间的优先级设置