Forum Discussion

047's avatar
047
Frequent Visitor
5 years ago
Solved

Calculate regional average based on selected Country

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 C...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
047
Frequent Visitor
5 years ago

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.