cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
v-yulgu-msft Super Contributor
Super Contributor

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
Super User
Super User

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

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

v-yulgu-msft Super Contributor
Super Contributor

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,

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 163 members 2,030 guests
Please welcome our newest community members: