DAX 等效于 T-SQL AVG OVER(PARTITION BY) [英] DAX Equivalent to T-SQL AVG OVER(PARTITION BY)

问题描述

抱歉，如果这是一件简单的事情，但在阅读了几篇类似的帖子后，我似乎找不到正确的答案.

Apologies if this is a simple thing to achieve but after reading several similar posts, I cannot seem to find the right answer.

我基本上想要做的是复制计算一组记录平均值的功能.

What I am basically trying to do is replicate the functionality of calculating an average over a group of records.

下面是一段简短的 SQL 来演示我想要达到的目的.

Below is a quick bit of SQL to demonstrate what I want to get to.

DECLARE @T TABLE(CountryID int, CategoryID int, ProductID int, Price float)

INSERT INTO @T VALUES
(1,20, 300, 10),
(1,20, 301, 11),
(1,20, 302, 12),
(1,20, 303, 13),
(1,30, 300, 21),
(1,30, 300, 22),
(1,30, 300, 23),
(1,30, 300, 24),
(2,20, 300, 5),
(2,20, 301, 6),
(2,20, 302, 7),
(2,20, 303, 8),
(2,30, 300, 9),
(2,30, 300, 8),
(2,30, 300, 7),
(2,30, 300, 6)

SELECT 
    *
    , AVG(Price) OVER(PARTITION BY CountryID, CategoryID) AS AvgPerCountryCategory
 FROM @t

这给了我我需要的结果......

Which gives me the results I require ...

CountryID   CategoryID  ProductID   Price   AvgPerCountryCategory
1           20          300         10      11.5
1           20          301         11      11.5
1           20          302         12      11.5
1           20          303         13      11.5
1           30          300         21      22.5
1           30          300         22      22.5
1           30          300         23      22.5
1           30          300         24      22.5
2           20          300         5       6.5
2           20          301         6       6.5
2           20          302         7       6.5
2           20          303         8       6.5
2           30          300         9       7.5
2           30          300         8       7.5
2           30          300         7       7.5
2           30          300         6       7.5

正如您所见，现在每一行都显示了相应国家/地区/类别的平均价格.在稍后阶段，这将用于计算与该平均值的差异，但现在我只想达到这一点并尝试自己锻炼接下来的步骤.

As you can see each row now shows the average Price for the respective Country/Category. At a later stage this will be used to calculate a variance from this average, but for now I'd just like to get to this point and try to workout the next steps myself.

那么在 DAX 中，AVG(Price) OVER(PARTITION BY CountryID, CategoryID) 的等价物是什么?

So what would bethe equivalent of AVG(Price) OVER(PARTITION BY CountryID, CategoryID) in DAX?

计划是，结果还将考虑应用于 Power BI 中数据的任何筛选器.我不确定这在现阶段是否重要.然而，这确实意味着在 SQL 中完成这项工作可能不是一种选择.

The plan is that the result will also take into account any filters that are applied to the data in Power BI. I'm not sure if this is important at this stage. However this does mean that doing this work in SQL is probably not an option.

我对 DAX 非常陌生，因此任何建议的表达方式都非常受欢迎.

I'm very new to DAX so an explanation any suggested expression would also be very wlecome.

推荐答案

您可以创建一个新的计算列，如下所示:

You can create a new calculated column that gives you this as follows:

AvgPerCountryCategory =
    CALCULATE(AVERAGE('@T'[Price]),
              ALLEXCEPT('@T', '@T'[CountryID], '@T'[CategoryID]))

这意味着我们对 CountryID 和 CategoryID 与当前行中的 ID 值匹配的所有行取平均值.(它会删除 all 行上下文 except 对于那些.)

This is saying that we take the average over all rows where the CountryID and CategoryID match the ID values in the current row. (It removes all the row context except for those.)

这相当于这个版本:

AvgPerCountryCategory =
    CALCULATE(AVERAGE('@T'[Price]),
              ALL('@T'[ProductID], '@T'[Price]))

这一次我们告诉它要删除什么行上下文而不是保留什么.

This time we're telling it what row context to remove rather than what to keep.

另一种方法是删除所有行上下文，然后明确删除您想要返回的部分:

Another way would be to remove all row context and then the parts you want back in explicitly:

AvgPerCountryCategory =
    CALCULATE(AVERAGE('@T'[Price]),
        ALL('@T'),
        '@T'[CountryID] = EARLIER('@T'[CountryID]),
        '@T'[CategoryID] = EARLIER('@T'[CategoryID]))

EARLIER 函数引用较早的行上下文.

The EARLIER function refers to the earlier row context.

这篇关于DAX 等效于 T-SQL AVG OVER(PARTITION BY)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！