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

RANKX Randomly Skipping Numbers

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Chase Frequent Visitor
Frequent Visitor

Re: RANKX Randomly Skipping Numbers

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: RANKX Randomly Skipping Numbers

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Chase Frequent Visitor
Frequent Visitor

Re: RANKX Randomly Skipping Numbers

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 334 members 3,549 guests
Please welcome our newest community members: