Reply
zvm Regular Visitor
Regular Visitor
Posts: 26
Registered: ‎11-13-2017
Accepted Solution

Calculate average on different levels

[ Edited ]

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!

 


Accepted Solutions
Community Support Team
Posts: 5,684
Registered: ‎09-21-2016

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.

View solution in original post


All Replies
Established Member
Posts: 150
Registered: ‎10-26-2018

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

zvm Regular Visitor
Regular Visitor
Posts: 26
Registered: ‎11-13-2017

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
Posts: 5,684
Registered: ‎09-21-2016

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.
Highlighted
zvm Regular Visitor
Regular Visitor
Posts: 26
Registered: ‎11-13-2017

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,