Need to correct sequence of Rank on excluding the value selected in slicer

[vijay_27]

I have three tables in my data model:

Calendar Table: Having date Column
Fact Table: Having Branch, Dist, Order Date, Region, Qty Columns
Branch Table:Having Branch Column

In Report,

A Filter for Branch from Branch Table

A Table visual is created in which we are calculating Rank based on a Measure below:

6 Month Capacity = (CALCULATE(SUM('Fact'[Qty]),calendar[Date]=EOMONTH(TODAY(),-1))
+ CALCULATE(SUM('Fact'[Qty]),calendar[Date]=EOMONTH(TODAY(),-2))
+ CALCULATE(SUM('Fact'[Qty]),calendar[Date]=EOMONTH(TODAY(),-3))
+ CALCULATE(SUM('Fact'[Qty]),calendar[Date]=EOMONTH(TODAY(),-4))
+ CALCULATE(SUM('Fact'[Qty]),calendar[Date]=EOMONTH(TODAY(),-5))
+ CALCULATE(SUM('Fact'[Qty]),calendar[Date]=EOMONTH(TODAY(),-6)))

Rank is calculated as below:

Rank =
VAR Ran =
RANKX(ALL('Fact'[Branch],'Fact'[Dist],'Fact'[Region]),'Table'[6 Month Capacity],,desc)
Return ran

I have created another measure to exclude the branch in Table whatever is selected in the filter:

Exclude Branch = IF(MAX('Fact'[Branch]) IN ALLSELECTED(Branch[Branch])&&
COUNTROWS(ALLSELECTED(Branch[Branch])) <> COUNTROWS(ALL(Branch[Branch])),
"Y","N")

When I put Exclude Branch="N" in filter pane, then Rank got disturbed:

Is there way to correct this Rank when we exclude the branch in table based on filter selection, or either way
to create any index such that it in sequence with no rank missing.

Please find the .pbix attachment.
https://drive.google.com/file/d/1ZIPB_KDNXsSZsucjDH91j8HvhoM9Jm4p/view?usp=sharing

Below output I want

[v-rzhou-msft · Accepted solution]

Hi @vijay_27 ,

You just need to add a filter in your Rank measure.

Try this code, it will still work even if you want to do multiple selection in your slicer.

Rank2 = 
VAR _SelectBranch =
    VALUES ( Branch[Branch] )
VAR _RANK =
    RANKX (
        FILTER ( ALL ( 'Fact' ), NOT ( 'Fact'[Branch] IN _SelectBranch ) ),
        [6 Month Capacity],
        ,
        DESC
    )
RETURN
    _RANK

Result is as below.

Select "t" in slicer:

Select "e" and "t" in slicer:

Best Regards,
Rico Zhou

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

[CNENFRNL]

Easy one; what's more, viz-level filter is not necessary. One measure does the trick,

[vijay_27]

Along with rank we also need 6 month capacity

[amitchandak]

@vijay_27 , I doubt you can make exclude work easily without using an independent table 

I have discussed an example here

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE