Calculate average on different levels

[zvm]

Hello,

I have one DAX question. Here is an example:

Country      Product       Price

Fra	P2	15
Fra	P3	19
Ger	P1	16
Ger	P3	20
Ita	P1	10
Ita	P2	11
Ita	P3	12
Ita	P4	10

I want to calculate average price of products per country (that's easy :)) and total average, but for total average each country should have the same weight factor. IE. I want average of average price per country for products.

I accomplished that as well. Here it is:

AvgPrice = AVERAGE(Table1[Price])

AvgOfAvg = AVERAGEX(VALUES(Table1[Country]) ; [AvgPrice])

My question is: is it possbile to obtain the same result with CALCULATE as with  AVERAGEX? I simply cannot get the context right on a grand total level.

Also, I can achieve the same with addiotional query with GROUP BY or SUMMARIZE. But I wanted to create solution within a single measure.

So, this is more like DAX quiz, since I basically solved my initial task (or problem). 🙂

Thanks!

[v-yulgu-msft · Accepted solution]

Hi @zvm,

Please try this measure:

average of average =
VAR temptable =
    SUMMARIZE ( Test2, Test2[Country], "A", AVERAGE ( Test2[Price] ) )
RETURN
    SUMX ( temptable, [A] ) / COUNTX ( temptable, [Country] )

Best regards,

Yuliana Gu

[zvm]

Thanks Yuliana

Not exactly what I asked for (calculate), but interesting hint for the other way to solve this otr for some other scenario,

[Anonymous]

You can nest row context inside iterations like AVERAGEX.  Try this:

Avg Price = AVERAGE('Avg of Avg'[Price])

Avg of Average = 
AVERAGEX(
        VALUES('Avg of Avg'[Country]), 
            AVERAGEX(
                VALUES('Avg of Avg'[Product]),[Avg Price]
            )
)

[zvm]

Thanks Nick.

I know a solution with averagex. I asked if there is a solution with Calculate to achieve the same result. 🙂