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

问题描述

道歉，如果这是一件简单的事情，但在阅读了几篇类似的文章之后，我似乎找不到正确的答案。

我基本上是想做的事情是重复的功能，可以计算一组记录的平均值。

下面是一段快速的SQL，演示了我想要得到的结果。

b
$ b

  DECLARE @T TABLE（CountryID int，CategoryID int，ProductID int，价格浮动）
 
插入@T值
（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 
  

哪个g为我提供所需的结果...

  CountryID CategoryID ProductID价格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 30300 22 22.5 
 1 30 300 23 22.5 
 1 30 300 24 22.5 
 2 20 300 5 6.5 
 2 20301 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 
  

如您所见，现在每一行都显示了相应国家/地区的平均价格。在以后的阶段中，这将用于计算该平均值的方差，但是现在，我只想了解这一点，并尝试自己尝试下一步。

那么DAX中 AVG（Price）OVER（PARTITION BY CountryID，CategoryID）等于什么？

<计划是该结果还将考虑应用于Power BI中数据的任何过滤器。我不确定在现阶段这是否重要。但这确实意味着用SQL进行这项工作可能不是一个选择。

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

解决方案

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

  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 函数引用较早的行上下文。

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.

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.

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

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.

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]))

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.)

This is equivalent to this version:

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]))

The EARLIER function refers to the earlier row context.

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