cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate average on different levels

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Calculate average on different levels

Hi @zvm,

```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

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

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.

Super Contributor

## Re: Calculate average on different levels

Hi @zvm,

```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
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,

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 127 members 1,543 guests
Recent signins: