RANKX Randomly Skipping Numbers

[Chase]

Hello, I have been working to build a calculated measure that will restart a rank set based on the relative hierarchy of my company's locations. It will rank the Divisions separately from the Regions, it will rank the Regions separately from the Districts, and it will rank the Districts separately from the individual Locations.

I have accomplished this by using the following DAX:

CY_WTD_Net_Revenue_Comp_YoY_Variance_Rank = IF(
    ISFILTERED(dim_Location[LocationID]),
    RANKX(ALL(dim_Location[LocationID]),CALCULATE(SUM(Revenue_Table[CY_WTD_Net_Revenue_Comp_YoY_Variance])),,DESC,Dense),
    IF(
        ISFILTERED(dim_Location[DistrictName]),
        RANKX(ALL(dim_Location[DistrictName]),CALCULATE(SUM(Revenue_Table[CY_WTD_Net_Revenue_Comp_YoY_Variance])),,DESC,Dense),
        IF(
            ISFILTERED(dim_Location[RegionName]),
            RANKX(ALL(dim_Location[RegionName]),CALCULATE(SUM(Revenue_Table[CY_WTD_Net_Revenue_Comp_YoY_Variance])),,DESC,Dense),
            IF(
                ISFILTERED(dim_Location[DivisionName]),
                RANKX(ALL(dim_Location[DivisionName]),CALCULATE(SUM(Revenue_Table[CY_WTD_Net_Revenue_Comp_YoY_Variance])),,DESC,Dense)
            )
        )
    )
)

This calculated measure works like a charm when used to SUM most columns. However, with this particular Year over Year variance column, it skips a number. For example:

Location -- Year over Year Variance -- Rank

UniqueLocationID #1 -- $199,863 -- 1

UniqueLocationID #2 -- $129,432 -- 2

UniqueLocationID #3 -- $99,580 -- 3

UniqueLocationID #4 -- $98,081 -- 4

UniqueLocationID #5 -- $91,458 -- 5

UniqueLocationID #6 -- $35,106 -- 6

UniqueLocationID #7 -- ($22,488) -- 8

UniqueLocationID #8 -- ($61,628) -- 9

UniqueLocationID #9 -- ($249,187) -- 10

As you can see, the rank skips the number 7. No other locations, other than the 9 unique ones listed, exist in the given district. I have replicated the ranking at the data layer as well with SQL and it ranks just fine, so it is not an underlying data issue. Furthermore, this issue only happens with certain districts, not all districts.

Does anyone know why/how this could be occurring? Is it the ISFILTERED() function?

As a side note, I do not think it has anything to do with the ALL() function. I have tried ALLNOBLANKROW() and ALLSELECTED() as well, and neither have affected the outcome.

[Chase · Accepted solution]

This thread was initially marked as spam, but has recently been unflagged. In order to dodge waiting a week for a moderator reply, I posted this issue earlier this week in a separate thread located here:

https://community.powerbi.com/t5/Desktop/RANKX-Randomly-Skipping-Numbers/m-p/659878#M316885

The solution found is in that thread as well.

[v-shex-msft]

Hi @Chase ,

Please share some sample data to help us clarify your data structure, it is hard to troubleshooting without any sample data to test.

How to Get Your Question Answered Quick

Regards,

Xiaoxin Sheng