tsql - DAX 等效于 T-SQL AVG OVER(PARTITION BY)

Question

如果这是一个简单的事情，但在阅读了几篇类似的帖子后，我似乎无法找到正确的答案。

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

下面是一些简单的 SQL 来演示我想要达到的目的。

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

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

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

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

那么什么将等同于 AVG(Price) OVER(PARTITION BY CountryID, CategoryID)在DAX？

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

我对 DAX 非常陌生，因此对任何建议的表达式的解释也将非常受欢迎。

Answer 1

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

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

这就是说我们取所有行的平均值，其中 CountryID和 CategoryID匹配当前行中的 ID 值。 (它删除 所有 行上下文 除了 那些。)

这相当于这个版本:

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

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

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

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

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