Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

RANKX conditional

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:

CustomerVolumeRank
A1004
B501
C302
D514
E283

 

How can I do this? 

 

Thaks!

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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.

1 Reply

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    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.