Calculate regional average based on selected Country

[047]

Hi

A newbie in DAX, I'm looking for some help to create a measure. The relevant tables are structured as below with Regions[Country] 1-->* Sales[Country] relation.

Sales

-----------

Product

Country

Total Sales

etc...

Regions

----------

Country

Region

Sub-Region

I have a slicer for Region[Country]. I wasnt to display in a card visual the total/avg sales for the selected country, and the corresponding figures for the region & subregion. E.g., If I select Australia, I want to display the figures for the country Australia, sub-region Oceania and Region ASIAPAC. I want to build a DAX measure for the logic below:

select avg(Sales[Total Sales]) where Sales[Country] in (select Country from Regions where Region in (select Region from Regions where Regions[Country] = SELECTEDVALUE(Sales[Country]) )))

I've tried to search this forum and in general without luck. Here's what I was trying which obviously isn't working becasue I'm only filtering on the selected country.

AvgPerRegion = calculate(AVERAGE(Sales[Total Sales]), filter('Sales',search(SELECTEDVALUE(Regions[Country]),Sales[Country],1,BLANK()))))

Thanks

[Anonymous]

Hi @047 ,

It's hard to provid DAX help without sample data. Please share some sample data and expected result to us.

Best Regards,

Jay

[047]

Hi @Anonymous 

Here's a quick sample. these are the two tables:

Sales			Regions
Product	Country	Total Sales	Country	Region	Subregion
A	Australia	100	Australia	APAC	Oceania
A	Austria	200	New Zealand	APAC	Oceania
B	New Zealand	100	Germany	EUR	Central Europe
C	Germany	400
A	Belgium	300

I have a slicer on Regions[Country] and a card visual which shows the avg value for a country, region and subregion. For example, If I select Australia on the slicer, I want the values to reflect for Australia, APAC & Oceania...something like this, but the country list should be populated based on the selected country from the slicer instead of hardcoding the values like the example below.

Subregion Sales = CALCULATE(AVERAGE('Sales'[Total Sales]), 'Sales'[Country] IN {"New Zealand", "Australia", "New Caledonia"})

I've tried my hand at it without luck...

temp =   

var FilteredCountryList = FILTER('Regions', Regions[Subregion] = FILTER(Regions, Regions[Country] = SELECTEDVALUE(Regions[Country])))

AvgRegion = calculate(AVERAGE('Sales'[total sales]),'Sales'[Country] in FilteredCountryList)))

Thanks

[Anonymous · Accepted solution]

Hi @047 ,

Create a relationship between these two tables. Then create below measures.

country sales = CALCULATE(SUM(Sales[Total Sales]),FILTER(Sales,Sales[Country] = SELECTEDVALUE(Regions[Country])))

region sales = 
var _region = CALCULATE(MAX(Regions[Region]),FILTER(Regions,Regions[Country]=SELECTEDVALUE(Regions[Country])))
return
CALCULATE(SUM(Sales[Total Sales]),FILTER(ALL(Regions),Regions[Region] = _region))

subregion sales = 
var _subregion = CALCULATE(MAX(Regions[Subregion]),FILTER(Regions,Regions[Country]=SELECTEDVALUE(Regions[Country])))
return
CALCULATE(SUM(Sales[Total Sales]),FILTER(ALL(Regions),Regions[Subregion] = _subregion))

Best Regards,

Jay

[047]

Indeed, it worked for me. I just had to add another another condition to clear the filter context on Sales, i.e. ALL(Sales).

Thanks for your help.

[lbendlin]

This is the standard "filtering up" pattern. Your measures need to calculate the parent entity(entities) and then modify the filter context accordingly.  It's a two (or more) step process best done with variables.