DAX Equivalent to T-SQL AVG OVER(PARTITION BY)

Question

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.

Answer 1

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.