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.

[OwenAuger · Accepted solution]

@Chase 

I've seen similar behaviour in the past where the table provided as the first argument of RANKX includes values that are not present in the filter context.

For example, the table ALL ( dim_Location[Location_ID] ) may include Location_ID values that don't relate to any rows of the fact table in the current filter context.

The result is that when the expression (2nd argument of RANKX) is evaluated for these location values, the result will be blank which is ranked the same as zero. In situations where you're only dealing with positive-valued expressions, you normally wouldn't notice, as zeros would be ranked after all the "valid" positive values.

However in your case, the variance expression takes positive and negative values, and blank expressions will be ranked the same as zero. A tell-tale sign that this has happened is that the "missing" rank occurs between a positive and negative value (between Location #6 & #7 in your example).

One solution is to modify the ALL ( ... ) expressions to include only values that relate to rows present in the fact table, using SUMMARIZE/ALL.

For example, replace

ALL ( dim_Location[LocationID] )

with

CALCULATETABLE (
    SUMMARIZE ( Revenue_Table, dim_Location[LocationID] ),
    ALL ( dim_Location[LocationID] )
)

 and so on for all your other ALL ( ... ) expressions.

Does that fix the problem?

Regards,

Owen

[Keegan_Patton]

Using Summarize is brilliant, thank you.

[Chase]

This fixed the problem! Thank you so much, Owen. I really appreciate it. I was thinking that there was a missing blank value, but I had convinced myself that ALLNOBLANKROW() would have fixed that issue if it existed. Had no idea that this CALCULATETABLE/SUMMARIZE methodology would do the trick.

Again, thanks!

[v-danhe-msft]

Hi @Chase ,

I could not reproduce your problem, it semmed work on my side:

Could you please offer more information about your data to have a test?

Regards,

Daniel He