cancel
Showing results for
Did you mean:
zvm 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 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

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

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