Forum Discussion

zvm's avatar
zvm
Helper II
7 years ago
Solved

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 :)) 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!

 

Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

    • zvm's avatar
      zvm
      Helper II
      7 years ago

      Thanks Nick.

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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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

    • zvm's avatar
      zvm
      Helper II
      7 years ago

      Thanks Yuliana

       

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