RANKX value changes for filtered value when it shouldnt

[rcorn]

I am using a RANKX formula to rank a category by UPB($). As shown in the screenshot I have two tables, the first table with only the green highlighted category with the rank 8, and the second table with 4 categorys with the green highligheted category being 7. 7 is the right rank for this category as shown in table 2, but when I click the green cateogry in table 2 to filter down table 1 to show only the green category value in table 1 it changes the rank to the wrong rank of 8.

This is my rank formula: 

Current Rank =

RANKX(ALL(table[category]),[SUM of UPB($)],,,Skip)

Please help me understand why when I click the green category in table 2 (with the right rank) why does that change the rank in table 1 when it should be the same.

[Anonymous]

Hi @rcorn ,

To avoid this issue, you can modify your formula to use the ALLSELECTED function instead of the ALL function. The ALLSELECTED function returns all the selected values in the current filter context, including any filters applied to the table. Here is the modified formula:

Current Rank =
RANKX(ALLSELECTED(table[category]),[SUM of UPB($)],,,Skip)

Best Regards,

Stephen Tao

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

[rcorn]

Thanks for the response, ALLSELECTED will not work in this case as it limits ranking to only the green category in the first table showing rank 1 where I want it to continue to show correct rank of 7 (as shown in table 2)

[PeterChen]

Having same issue.... Have you solved it?