Dynamic ranking of values selected in slicer, and not rank blank values

[Benjwill]

Hi, this is my simplified ranking problem:

I have a fact table which consists of 3 columns in power BI: Company, Product, and Admin fees. Product is like a primary key of the table. One company can have more than one product, and Admin fees can be blank on some products. I want to create a table visual of this very table, adding a rank measure or column to a table visual of this table, that does the following two things: 1. do not assign a rank to the blank values. 2. when a certain company is selected in the slicer, the rank column would rank only the values displayed for the products on screen. it is like dynamic ranking. Can you help write the dax for this rank measure?

I've tried a number of ways but it didn't work.

I've attached my pbix here.

https://drive.google.com/file/d/1o66ym_LCNzv_cAAwYp6z4XXST7NVyZe7/view?usp=sharing

Below is one measure I've tried: 

Rank Admin Fees =

RANKX(

    ALLSELECTED('FACT TABLE'[Product]),

    CALCULATE(SUM('FACT TABLE'[Admin Fees]),ALLEXCEPT('FACT TABLE','FACT TABLE'[Product])), ,ASC,Dense)

This is my result. Is there a way for blanks to not be ranked? (although blanks must still remain on screen, since we have other columns in the real data that need to be ranked, and on the same table)

Secondly, I would like the rank to be dynamic like this, when company D is chosen on slicer:

[Benjwill]

Hi thank you greatly for the fast response. However it is not working, not sure if I missed anything.

I put your measure into the model as Rank Admin Fees 2.

It produces rank 2 for all values 😞

[Anonymous · Accepted solution]

Hi @Benjwill ,
According to your needs, you can first use if statement to exclude the part of the value is empty first, and then sort the remaining data, the following is some of our ideas hope to help your problem!

Measure = 
IF(
    ISBLANK(MAX('FACT TABLE'[Admin Fees])),
    BLANK(),
    RANKX(
        FILTER(
            ALLSELECTED('FACT TABLE'),
            NOT ISBLANK('FACT TABLE'[Admin Fees])  
        ),
        'FACT TABLE'[Admin Fees],                 
        MAX('FACT TABLE'[Admin Fees]),             
        ASC,                                       
        DENSE                                     
    )
)

If you have any other questions, you can check out the pbix file I uploaded, I hope it helps, and I'd be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Benjwill]

Thank you a ton Tom, it worked!

[anmolmalviya05]

Hi @Benjwill, Please try the below measure:

Rank Admin Fees =
VAR AdminFee = SUM('FACT TABLE'[Admin Fees])
RETURN
IF(
ISBLANK(AdminFee),
BLANK(),
RANKX(
FILTER(
ALLSELECTED('FACT TABLE'[Product]),
NOT(ISBLANK(SUM('FACT TABLE'[Admin Fees]))) 
),
CALCULATE(SUM('FACT TABLE'[Admin Fees])),,ASC, DENSE))