RANK function with two level filters

[michaldb]

Hello,

I have been trying to find a solution to the following problem, which I will explain using the sample report linked below:

RANK - Adventure Works DW 2020.pbix

I have a matrix visual with "Region" as the rows and a "Total Sales" measure:

Total Sales = SUM( Sales[Sales Amount] )

I want to rank the regions, which I can achieve using either of the following two measures:

Total Sales Rank ALL = 
RANK(
    SKIP,
    ALL( 'Sales Territory'[Region] ),
    ORDERBY( [Total Sales] )
)

Total Sales Rank ALLSELECTED = 
RANK(
    SKIP,
    ALLSELECTED( 'Sales Territory'[Region] ),
    ORDERBY( [Total Sales] )
)

The difference between them is in how they behave when I filter the regions:

• The "ALL" version retains the original rank based on the entire region lookup table, regardless of filtering.
• The "ALLSELECTED" version recalculates the rank based on the filtered lookup table.

Unfortunately, neither of these fully satisfies my requirements. Here is what I need:

1. I want to be able to filter the visual for any regions, but retain the original rank (as in the "ALL" version).
2. I also want to exclude certain regions from the lookup table before filtering with the slicer—essentially applying two levels of filtering.

Here’s an example:

First, I exclude one region, "Corporate HQ."

Notice that the "ALL" measure still counts the excluded region, so the ranking starts from 2 and goes up to 11.

The "ALLSELECTED" measure shows the rank as I expect but when when I filter on the region with the slicer, the ranks is recalculated among the visible regions.

What I would like is for the measure to show the same ranks as before filtering by regions with the slicer.

Instead of this:

I would like:

Region	Total Sales Rank Proper
Australia	7
France	3
Germany	1

So, I need the ranks calculated among all regions except "Corporate HQ".

Is there any way to achieve this? I’ve tried Googling and watching SQLBI videos, but the RANK functions seem a bit tricky. I also tried using the RANKX function, but without success.

You can find the sample report at the link I posted earlier.

I would greatly appreciate any suggestions.

[lbendlin · Accepted solution]

If you are always excluding Corporate HQ then you can use

Total Sales Rank ALL = 
RANK(
    SKIP,
    Filter(ALL( 'Sales Territory'[Region] ),[Region]<>"Corporate HQ"),
    ORDERBY( [Total Sales] )
)

[michaldb]

Thanks a lot! Ideally, I would have another slicer to choose regions to be excluded - maybe based on additional table added to the model but I just can't figure it out. But your solution will work for me too 🙂