cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hannahGala Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: RANKX excluding blanks

@hannahGala,

 

You may refer to DAX below.

YOY Rank =
IF (
    NOT ( ISBLANK ( [YOY] ) ),
    RANKX (
        FILTER ( ALLSELECTED ( 'Core ClubsVW'[Club] ), NOT ( ISBLANK ( [YOY] ) ) ),
        [YOY]
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: RANKX excluding blanks

@hannahGala,

 

You may refer to DAX below.

YOY Rank =
IF (
    NOT ( ISBLANK ( [YOY] ) ),
    RANKX (
        FILTER ( ALLSELECTED ( 'Core ClubsVW'[Club] ), NOT ( ISBLANK ( [YOY] ) ) ),
        [YOY]
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

hannahGala Frequent Visitor
Frequent Visitor

Re: RANKX excluding blanks

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

madawak Frequent Visitor
Frequent Visitor

Re: RANKX excluding blanks

Thanks Sam.

 

Regards,

Madawa

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,479)