CODEWITHSUNDEEP
powerbidax
superavd88
superavd88

Reputation: 77

DAX RANKX Returning all 1's

Tying to rank a calculated field in PowerBI using RANKX.

enter image description here

All fields come from the Sales table and the calculated field is YoY $ Growth which is basically Expanded Comm Sales - PY COMM SALES.

Watching a youtube video about the function RANKX I came up with the DAX expression below.

YTD Rank = RANKX(Sales,[YoY $ Growth],,DESC,Skip)

I'm getting all 1's when I run it like this. Can someone explain why this is happening and how to correctly use the formula?

Thanks!

Upvotes: 0

Views: 1776

Answers (2)

superavd88
superavd88

Reputation: 77

Thanks for the reply, I was actually able to solve it in a simpler way:

YTD Rank = RANKX(ALL(Sales[NAME]),[YoY $ Growth],,DESC,Skip)

It looks like RANKX basically loops through the table and ranks based on the NAME. It's working fine, my problem now is that I have values in name that need to be excluded like ~, Blanks, and let's say Person X. I now need to figure out how to exclude those in the DAX rather than excluding them from the table.

Upvotes: 1

msta42a
msta42a

Reputation: 3741

Try with:

YTD Rank =
VAR RoundedSales = [YoY $ Growth]
RETURN IF (
    HASONEVALUE ( Sales[Name] ) && ( RoundedSales > 0 ),

    VAR LookupTable =
        ADDCOLUMNS (
            ALLSELECTED ( Sales),
            "@CustomerSales",
                [YoY $ Growth]
        )
    VAR CurrentValue =
        RoundedSales 
    VAR Ranking =
        RANKX ( LookupTable, [@CustomerSales], CurrentValue,, DENSE )
    RETURN
        Ranking
)

here is a good article about rankx: https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

Upvotes: 0

Related Questions