Forum Discussion

hannahGala's avatar
hannahGala
Advocate I
8 years ago
Solved

RANKX excluding blanks

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's avatar
    v-chuncz-msft
    8 years ago

    hannahGala,

     

    You may refer to DAX below.

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

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    hannahGala,

     

    You may refer to DAX below.

    YOY Rank =
IF (
    NOT ( ISBLANK ( [YOY] ) ),
    RANKX (
        FILTER ( ALLSELECTED ( 'Core ClubsVW'[Club] ), NOT ( ISBLANK ( [YOY] ) ) ),
        [YOY]
    )
)
    • hannahGala's avatar
      hannahGala
      Advocate I
      8 years ago

      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