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?