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

[JDLee23]

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!

[v-yulgu-msft · Accepted solution]

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]

Hey @v-yulgu-msft,

silly me, thanks for the adjustment.

Regards

[JDLee23]

@v-yulgu-msft, @TomMartens - thanks very much for your help, much appreciated!

[TomMartens]

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