Missing rank values in MDX when ranking on calculated measures with division

Question

We are trying to rank measures using the RANK() function in our Report Builder dataset, but when the calculated measure being ranked has division in it, it randomly skips values. This is not related to the measure being equal in value then going to the next rank. We understand this is how the RANK() function works. For example it will go from rank 33, 34, then to 36 skipping 35 altogether, when the measure being ranked has unique values for each member.

None of the datasets, tables, or groupings are filtered or hiding any rows so we should see rank 35. The rank function works as expected on measures with addition, subtraction, and multiplication, but breaks on all of our rankings with division. The skipped values are random and are not always at the same place either for the different ranked measures.

We have also used custom report code to rank in the table itself and the division breaks that too.

Here are some examples of the measures used:

Sales Goal Attainment Metric

MEMBER [Measures].[SalesGoalAttainment] AS
IIF([Measures].[Sales_Target] is null,null,
([Measures].[Metric1_Sales_MTD] + [Measures].[Metric2_Sales_MTD] + ([Measures].[Metric3_Sales_MTD] *.5))
/ [Measures].[Sales_Target] )

Rank of Sales Goal Attainment

MEMBER [Measures].[SalesGoalAttainmentRank] AS
IIF( Measures].[SalesGoalAttainment] = null, null, 
RANK ([Regions and Stores].[Store Name].currentmember, [Regions and Stores].[Store Name].members, [Measures].[SalesGoalAttainment] ))

When we pull out the "/ [Measures].[Sales_Target]" portion of the measure being ranked it works fine.

Does anyone have a solution for ranking on measures with division without getting missing rank values? Or is this a known bug of some sort?

Haidong Huang Haidong Huang · Accepted Answer · 2021-03-05T21:47:23

The cause of the problem is that the Rank function ranks a member against a set that contains the ALL member in the following function call

RANK ([Regions and Stores].[Store Name].currentmember, [Regions and Stores].[Store Name].members, [Measures].[SalesGoalAttainment] )

[Regions and Stores].[Store Name].members references to the whole hierarchy that contains the ALL member. This can cause confusion since the ALL member is usually not supposed to be in the list of members to be ranked.

The solution is to use the following expression to rank the members by referencing the hierarchy level that does not contain the ALL member

RANK ([Regions and Stores].[Store Name].currentmember, [Regions and Stores].[Store Name].[Store Name].members, [Measures].[SalesGoalAttainment] ))

Missing rank values in MDX when ranking on calculated measures with division

1 Answers