RANKX Randomly Skipping Numbers

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