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 a table of Country, City, TotalSales, I want to show have a measure of the average TotalSales for both a selected city and the country which that city is in (to allow for easy comparison). However, the country is not explicitly selected anywhere, and as such BI seems to be unable to calculate the average...?

 

E.g. by selecting 'London', one card will show the average total for London, whilst the other card now shows the average for England.

 

Please note that these are in separate cards for presentation reasons, not a single table!

 

I seem to get somewhere with GROUPBY() and SUMMARIZE(), but my experience of DAX is too limited to fully understand how to do this.

 

Any help would be much appreciated, thank you in advance!

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

4 Replies

  • 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

  • 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