如何在 DAX 中计算多个周期的移动平均线

在 DAX 中计算移动聚合很容易。但是，计算一段时间内的移动平均值时会有一些陷阱。由于其中一些陷阱是定义问题，因此我们必须小心，不要选择错误的方法。让我们看看细节。欢迎来到雲闪世界。

添加图片注释，不超过 140 字（可选）

首先，一些数学知识

计算平均值很容易：将值的总和除以实例数。

虽然值的总和很容易，但实例的数量并不像您想象的那么简单。

例如，我们来看看下表：

图 1 — 数字列表

平均值列的计算很简单：

<值的总和> / <行数> = 534.68 / 10 = 53.47

现在，让我们删除一个值，这会改变图片。

图 2 — 有间隙的数字列表

突然，我有两种计算平均值的方法：

<值的总和> / <值的数量> = 547.23 / 9 = 60.8

或者

<值的总和> / <行数> = 547.23 / 10 = 54.72

第二种方法只是定义不同。 例如，假设第一列是客户 ID，我想计算所有客户的平均销售额或活跃客户的数量等。在这种情况下，第二种计算平均值的方法可能是更好的方法。

现在，让我们将其转换为 DAX。

移动聚合——起点

首先我们来构建移动聚合的典型案例。

我想要获得过去四个月的搬家销售额。

现在，我使用销售额的SUM()，因为验证四个月的结果比计算平均值要容易得多。

销售移动总和 = VAR MaxDate = MAX( '日期'[日期] ) VAR MinDate = CALCULATE( MIN('日期'[日期]) , DATEADD( '日期'[日期], - 3, MONTH ) ) VAR DateRange = CALCULATETABLE( DATESBETWEEN( '日期'[日期] ,MinDate ,MaxDate ) ) VAR Result = CALCULATE([在线销售总和] ,DateRange ) RETURN 结果

首先，我获取当前筛选上下文（例如当前月份）的最后一个日期

其次，我使用DATEADD()来回移三个月。由于我包括了当前月份，因此我只回移了三个月。

如果我想排除当前月份，我必须采取不同的做法。在这种情况下，我必须获取第一个日期并回溯一天以获取上个月的最后一个日期（或使用EOMONTH(MAX('Date'[Date), -1) ）。然后，使用 DATEADD() 回溯四个月）。

第三，我使用DATESBETWEEN()来获取两个变量之间的日期列表。

最后，我将日期列表传递给CALCULATE()以返回最终结果。

结果如下：

添加图片注释，不超过 140 字（可选）

我可以通过删除 DATESBETWEEN() 函数并将两个变量直接传递给 CALCULATE() 来简化测量：

VAR MaxDate = MAX( '日期'[日期] ) VAR MinDate = CALCULATE( MIN('日期'[日期]) , DATEADD( '日期'[日期], - 1, MONTH ) ) VAR Result = CALCULATE([在线销售额合计] ,'日期'[日期] >= MinDate && '日期'[日期] <= MaxDate ) 返回 结果

结果相同，但使用 DATESBETWEEN() 时性能略好（事实表中有 1700 万行）。

由于多种因素都会影响性能，我鼓励您在数据和用例中尝试这两种变体并检查差异。

让我们做平均值

现在，我终于开始计算平均值了。

我使用与在线销售额总计相同的逻辑，并使用 AVERAGEX() 来计算平均销售额：

平均在线销售额 = AVERAGEX('在线销售额' ,('在线销售额'[单价] * '在线销售额'[销售数量]) - '在线销售额'[折扣金额] )

接下来，我复制上面的度量来计算销售移动平均线，结果如下：

图 4 — 基本平均值和移动平均值的结果

我可以在这里结束并写下“任务完成”。但是别再说了。

一开始，我提到了计算平均值的不同方法。

因此，我开始编写度量值来测试它们。 我编写了以下度量值，将其用作分母，同时使用了 [在线销售额总和] 和提名人：

• 统计在线销售量 = COUNTROWS('在线销售量')

• 客户数量 = DISTINCTCOUNT('在线销售额'[CustomerKey])

• 在线订单数量 = DISTINCTCOUNT('在线销售额'[SalesOrderNumber])

结果变量的代码如下（以 [Count Online Sales] 为例）：

VAR 结果 = CALCULATE([在线销售总额] / [在线销售数量] ，日期范围 )

我可以想到更多的变体（例如，平均总体客户，甚至那些在这段时间内没有订单的客户）。但我决定就此打住，以免造成混淆。

毫不奇怪，他们每个人都得出了不同的结果：

图 5 — 所有变体的平均值结果

结果之间的差异非常大。

您可能会遇到计算平均值的其他方法。

因此，我强烈建议您明确定义如何计算平均值。否则，您可能会向受众提供意想不到的甚至不正确的结果。

月平均销售额

计算平均值时还有一个差异：按月销售额的平均值。

让我们再次看一下月销售额的结果：

图 6 — 仅月销售额

我想计算四个月的平均月销售额。

例如，在九月份，我想计算六月份、七月份、八月份和九月份的月销售额的平均值：

(275'061'552.33 + 303'302'950.82 + 273'004'268.56 + 262'971'889.59) / 4 = ~278'585'165.3 (~ 因为舍入差异我们可能会得到略有不同的结果)

为了满足这个要求，我必须思考如何去做。

我需要提前计算每个月的销售额。然后只考虑每行所需的四个月。最后，计算这四个值的平均值。

这意味着我必须首先生成一个包含所有月度结果的表格，然后仅使用计算平均值所需的值，这是低效的。然后，Power BI 将为表格中的每一行计算此值以可视化结果。

当我从每行的筛选上下文的角度来看待它时，我可以做得更好。

为什么不仅计算可视化中每行相关的月份的销售总额？

基于这种方法，我写了以下措施：

按月移动平均值 = // 1. 获取当前筛选上下文的第一个和最后一个日期 VAR MaxDate = MAX( 'Date'[Date] ) VAR MinDate = CALCULATE( MIN('Date'[Date]) , DATEADD( 'Date'[Date], - 3, MONTH ) ) // 2. 生成移动平均值所需的日期范围（四个月） VAR DateRange = CALCULATETABLE( DATESBETWEEN( 'Date'[Date] ,MinDate ,MaxDate ) ) // 3. 生成按步骤 2 生成的日期范围过滤的表格 // 此表仅包含四行 VAR SalesByMonth = CALCULATETABLE( SUMMARIZECOLUMNS( 'Date'[MonthKey] , "#Sales", [Sum Online Sales] ) ,DateRange ) RETURN // 4. 计算平均值步骤 3 中生成的表中的四个值 AVERAGEX(SalesByMonth, [#Sales])

这次，我添加了内嵌注释来解释那里发生的事情。

结果如下：

图 7 — 每月平均结果

我在Excel中检查了结果，它们是正确的。

如果您正在考虑创建一个每月预先计算的表格，请再考虑一下。

您将被迫添加对所有维度的引用，并增加数据的粒度，无论如何您都需要编写此度量来满足受众通过所有维度过滤数据的需求。

该解决方案非常高效，计算结果仅需不到0.4秒。

即使扩大所有月份，结果也不需要更多时间来计算。

顺便说一句，这种方法也适用于计算平均值的平均值。

结论

平均值不等于平均值​​。我想这是很清楚的。

但更重要的是，了解应该计算什么以及如何计算至关重要。

理解为什么必须计算一个数字可以帮助你选择正确的计算逻辑。

当逻辑清晰时，应该定义编写 DAX 代码的方法。记住要从筛选上下文的角度来做。有时，这是违反直觉的，但它有助于开发高效的代码。

我希望你今天学到了一些新东西。

感谢关注雲闪世界。（Aws解决方案架构师vs开发人员&GCP解决方案架构师vs开发人员）

添加图片注释，不超过 140 字（可选）