How to rank within a hierarchy but ignoring upper levels

[NM2023]

Dear all, 

I want to do dynamic ranking within a matrix. I can easily achieve ranking within region (green column), and my question is, how to achieve the ranking across regions (yellow column)? It doesnt seem like the evaluation can break the upper region context. 

Ranking =
var territoryRanking = rankx(allselected(salesHier[Territories]), [TotalSales])
var RegionRanking = rankx(allselected(salesHier[Regions]), [TotalSales])
return if(isinscope(salesHier[Territories]), territoryRanking, if(isinscope(salesHier[Regions]), RegionRanking, blank()))

Thank you so much!
NM

[Anonymous · Accepted solution]

Your ideas is great @lbendlin

Hi, @NM2023 

I created a matrix using the following sample data:

I created a measure using the following DAX expression:

Measure = 
IF(ISINSCOPE('Table'[territories]),RANKX(ALLSELECTED('Table'),'Table'[Measure 2]))

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Use REMOVEFILTERS() on the Regions and Territories.

[Wilson_]

NM,

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂