Forum Discussion

JDLee23's avatar
JDLee23
Helper I
9 years ago
Solved

Calculate an average based on an implicit filter by a second column

Hi,   I am trying to create a measure which will allow me to show an average, based on the selection in a slicer. However, the slicer only implicitly selects the group of data, for example -   In...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi JDLee23,

     

    Thanks for TomMartens's suggestion, it works fine in my test. But, it is not able to directly define a column name for variable, so, I made some modification:

    Avg Sales Country = 
var currentCountry = LASTNONBLANK('table'[Country],1)
return
CALCULATE(
    AVERAGE('table'[TotSales])
    ,ALL('table'[City])
    ,'table'[Country] = currentCountry
)

    Best regards,
    Yuliana Gu

TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

If there is a slicer that selects the city, these measures should work

Avg Sales City = 
CALCULATE(
  AVERAGE('table'[TotSales])
)

and

Avg Sales Country = 
var currentCountry = 'table'[Country]
return
CALCULATE(
    AVERAGE('table'[TotSales])
    ,ALL('table'[City])
    ,'table'[Country] = currentCountry
)

 

Hope this helps