RANKX value changes for filtered value when it shouldnt

Question

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 · Answer

Hi&nbsp;rcorn&nbsp;,
&nbsp;
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)
&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

rcorn · Answer

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 · Answer

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

Forum Discussion

RANKX value changes for filtered value when it shouldnt

3 Replies

Related content

Filter

DAX Change Filter Context

filtered calculation changes the values

Dynamic Filter

Inverse Filter