RANKX conditional

[Anonymous]

Hello,

I need some help, I want to create a measure with RANX, but with condition. 

I have a table with Customer and Volume, I need top rank by Volume, but the customer A and D need to be the last in the rank , as bellow:

Customer	Volume	Rank
A	100	4
B	50	1
C	30	2
D	51	4
E	28	3

How can I do this? 

Thaks!

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Put this formula in as a calculated column on your table.

Ranking = 
VAR BottomCustomers = 
    DATATABLE (
        "Customer",STRING,
        {
            {"A"},
            {"D"},
            {"Add any other customer as a new line"},
            {"One line for each customer to rank on the bottom"}
        }
    )
RETURN
RANKX (
    FILTER ( ALL ( Table1 ), NOT ( Table1[Customer] IN BottomCustomers ) ),
    IF ( Table1[Customer] IN BottomCustomers, -999999, Table1[Volume] )
)

I put a couple extra lines in the list of customers incase you end up needing to add more you can see where they go.