辽宁住房和城乡建设厅网站,wordpress 手机端发帖,jsp网站建设项目实战课后,wordpress教材在 DAX 中计算移动聚合很容易。但是#xff0c;计算一段时间内的移动平均值时会有一些陷阱。由于其中一些陷阱是定义问题#xff0c;因此我们必须小心#xff0c;不要选择错误的方法。让我们看看细节。欢迎来到雲闪世界。 添加图片注释#xff0c;不超过 140 字#xff08…在 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 — 仅月销售额
我想计算四个月的平均月销售额。
例如在九月份我想计算六月份、七月份、八月份和九月份的月销售额的平均值
(275061552.33 303302950.82 273004268.56 262971889.59) / 4 ~278585165.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 字可选
