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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
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