cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zvm Regular Visitor
Regular Visitor

Calculate average on different levels

Hello,

 

I have one DAX question. Here is an example:

 

Country      Product       Price

FraP215
FraP319
GerP116
GerP320
ItaP110
ItaP211
ItaP312
ItaP410

 

I want to calculate average price of products per country (that's easy Smiley Happy) 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:

Izrezak.PNG

 

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

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate average on different levels

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Nick_M Established Member
Established Member

Re: Calculate average on different levels

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

Avg of Average.png

Highlighted
zvm Regular Visitor
Regular Visitor

Re: Calculate average on different levels

Thanks Nick.

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

Community Support Team
Community Support Team

Re: Calculate average on different levels

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
zvm Regular Visitor
Regular Visitor

Re: Calculate average on different levels

Thanks Yuliana

 

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