SSAS MDX rank HUGE skip when ranked metric goes from negative to positive

Question

Using this code , the following code, the rank produced works perfectly between 1 and 50 and 204 and 400 , but at 50, at the moment the ranked metric goes from negative to positive, it SKIPS to 204. The MDX creates an ordered set, then ranks by that set. the calculated member creating the rank is called [measures].[Level5_Rank] the (masked) data looks like this, note the rank skips at the precise moment that the ranked metric goes from negative to positive.

Or could it be ranking other members outside the query context?

Fall joe -1.80E-02 205 Fall john -1.38E-02 204 (rank skips here!!!) Fall bob 9.12E-03 51 Fall billy 6.13E-02 50

with



 member [Measures].[CYTD Cancel Rate] as
 iif([Measures].[FC Actual PC] + [FC Cancelations PC] = 0, null,
 ( [FC Cancelations PC]/([Measures].[FC Actual PC] + [FC Cancelations PC]) ) )
 , FORMAT_STRING = 'Percent'

 member [measures].[CYTD Cancel Rate vs Goal] as
 IIF(KPIGoal("FC Cancels")=null,null,([Measures].[FC Cancel Rate GL] - [measures].[CYTD Cancel Rate]) * 100)



 set [OrderedLevel5Members] as [Performance Rep Team Org].[Reporting Org Hrchy].[Level5].members 

**member [measures].[Level5_Rank] as IIF(KPIGoal("FC Cancels")=null,null,rank([Performance Rep Team Org].[Reporting Org Hrchy].CurrentMember,OrderedLevel5Members,[Measures].[CYTD Cancel Rate vs Goal]) )**


 member [measures].[Level5] as 
 IIf([Performance Rep Team Org].[Reporting Org Hrchy].CurrentMember.level.ordinal > 4
    ,ancestor([Performance Rep Team Org].[Reporting Org Hrchy].CurrentMember,[Performance Rep Team Org].[Reporting Org Hrchy].[Level5]).member_caption
    ,null
    ) 




 SELECT 
    {
      [measures].[Level5]


        ,[measures].[CYTD Cancel Rate vs Goal]
        ,[measures].[Level5_Rank]

   } ON COLUMNS
 ,NON EMPTY 
    nonempty({
    [Season].[Season].[Season].AllMEMBERS *
        [Performance Rep Team Org].[Reporting Org Hrchy].ALLMEMBERS


    }
        ,{[Measures].[FC Actual PC]}     
    ) 

    DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS 
FROM 
(SELECT
(
{
    [Performance Rep Team Org].[Reporting Org Hrchy].allmembers

}
)ON COLUMNS
  FROM [Book Fairs Sales]
  )

WHERE
( 
{[Fiscal Year].[Fiscal Year].&[124]
},
{[As Of Time].[Fiscl Hrchy].[Date].&[2012-05-31T00:00:00]
} 
, 
{
[Sales Org Hier Time].[Fiscl Hrchy].[Date].&[2012-05-31T00:00:00]} 
)

Answer 1

i needed to use non empty in the set before ranking

WITH 
  MEMBER [Measures].[CYTD Cancel Rate] AS 
    IIF
    (
      [Measures].[FC Actual PC] + [FC Cancelations PC] = 0
     ,null
     ,
      [FC Cancelations PC] / ([Measures].[FC Actual PC] + [FC Cancelations PC])
    ) 
   ,FORMAT_STRING = 'Percent'
  MEMBER [measures].[CYTD Cancel Rate vs Goal] AS 
    IIF
    (
        KPIGoal("FC Cancels")
      = NULL
     ,null
     ,
      ([Measures].[FC Cancel Rate GL] - [measures].[CYTD Cancel Rate]) * 100
    ) 
  SET [OrderedLevel5Members] AS 
    NonEmpty
    ([Performance Rep Team Org].[Reporting Org Hrchy].[Level5].MEMBERS 
    ,[Measures].[CYTD Cancel Rate vs Goal])

  MEMBER [measures].[Level5_Rank] AS 
    IIF
    (
        KPIGoal("FC Cancels")
      = NULL
     ,null
     ,Rank
      (
        [Performance Rep Team Org].[Reporting Org Hrchy].CurrentMember
       ,OrderedLevel5Members
       ,[Measures].[CYTD Cancel Rate vs Goal]
      )
    ) 

SELECT 
  {
    [measures].[CYTD Cancel Rate vs Goal]
   ,[measures].[Level5_Rank]
  } ON COLUMNS
,
--NON EMPTY 

    NonEmpty
    (
      {

          [Performance Rep Team Org].[Reporting Org Hrchy].ALLMEMBERS
      }
     ,{[Measures].[FC Actual PC]}
    )

   ON ROWS
FROM 
(
  SELECT 
    {
      [Performance Rep Team Org].[Reporting Org Hrchy].ALLMEMBERS
    } ON COLUMNS
  FROM [Book Fairs Sales]
)
WHERE 
  (
  [Season].[Season].&[Fall]
    ,[Fiscal Year].[Fiscal Year].&[124]
   ,[As Of Time].[Fiscl Hrchy].[Date].&[2012-05-31T00:00:00]
   ,
      [Sales Org Hier Time].[Fiscl Hrchy].[Date].&[2012-05-31T00:00:00]

  );

http://social.msdn.microsoft.com/Forums/eu/sqlanalysisservices/thread/1264dbe8-bc6a-46d5-89b1-6d8825929ba0