RANKX excluding blanks

[hannahGala]

Hiya all,

I've not posted before, apologies if this isn't in the right place.

I want to rank clubs by their YOY (year on year) variances - however there are some which may not have any sales this year or last year, in the period/category that has been chosen by the user in the slicer.

I am getting:

Club     YOY     Rank

------- -------- -------

Club 1   +£500   1

Club 2   +£200   2

Club 3           3

Club 4   -£100   4

YOY is a calculated measure.

This is what I have now :

YOY Rank = RANKX (ALL('Core ClubsVW'),[YOY])

How can I show this as :

Club    YOY      YOY Rank

------- -------- ----------

Club 1   +£500   1

Club 2   +£200   2

Club 4   -£100   3

Club 3           

Please could someone help me?!

Thanks

Hannah

[v-chuncz-msft · Accepted solution]

@hannahGala,

You may refer to DAX below.

YOY Rank =
IF (
    NOT ( ISBLANK ( [YOY] ) ),
    RANKX (
        FILTER ( ALLSELECTED ( 'Core ClubsVW'[Club] ), NOT ( ISBLANK ( [YOY] ) ) ),
        [YOY]
    )
)

[madawak]

Thanks Sam.

Regards,

Madawa

[hannahGala]

Love this, thank you! Couldn't have done it without your help 🙂

I've amended it slightly so that it:

1) doesn't filter on selected (I want to show estate rank, even if you choose region in a slicer)

2) has a filter on open clubs 

3) only shows if hasonevalue (so that it doesn't show a total at the bottom)

YOY Estate Rank = 
    IF(
        HASONEVALUE('Core ClubsVW'[CostCentre]),        
        IF (
            NOT ( ISBLANK ( [YOY] ) ),
            RANKX (
                FILTER ( ALL ( 'Core ClubsVW' ), NOT ( ISBLANK ( [YOY] ) ) && 'Core ClubsVW'[OpenFlag]=1),
                [YOY]
            )
        ),
        "")

Thanks SO much.

Hannah