Ranking of multiple dimensions, restarting for every year

Question

I have a measure, Sales Amount. I want to rank customers within a divison by year for that measure. I need to also display that rank as a measure. The rank needs to start over every year. I am able to do customers by year and customers by division, but I can't seem to figure out how to combine them both so it iterates over both dimensions properly. Below is what I have for the customers by year. I have tried adding another Division set, creating another named set that I GENERATE with the YearsWithCustomers set, and RANK using that new named set. I seem to be super close to figuring this out but I think I am putting something in the wrong place. I got the idea to iterate over a set from one of Chris Webb's blogs, located here.

WITH 
  SET Years AS 
    TopPercent
    (
      [Sales and Forecast Date].[Calendar Year].[Year Number].MEMBERS
     ,100
     ,[Measures].[Sales Amount]
    ) 
  SET Customers AS 
    Filter
    (
      [Customer].[Customer Number].[Customer Number].MEMBERS
     ,
      [Measures].[Sales Amount] > 0
    ) 
  SET YearsWithCustomers AS 
    Generate
    (
      Years
     ,Union
      (
        {[Sales and Forecast Date].[Calendar Year].CurrentMember}
       ,StrToSet
        ("
            Intersect({},
           {order(Customers,([Sales Amount],[Sales and Forecast Date].[Calendar Year].CurrentMember),desc)
            as CustomerSet"
            + 
              Cstr(Years.CurrentOrdinal)
          + "})"
        )
      )
     ,ALL
    ) 
  MEMBER [Measures].[Customer Rank] AS 
    Rank
    (
      [Customer].[Customer Number].CurrentMember
     ,StrToSet
      ("CustomerSet"
        + 
          Cstr
          (
            Rank
            (
              [Sales and Forecast Date].[Calendar Year].CurrentMember
             ,Years
            )
          )
      )
    ) 
SELECT 
  {
    [Customer Rank]
   ,[Measures].[Sales Amount]
  } ON 0
 ,Order
  (
    Filter
    (
      (
        YearsWithCustomers
       ,Customers
      )
     ,
      [Sales Amount] > 0
    )
   ,[Sales Amount]
   ,desc
  ) ON 1
FROM [OrdersAndBudgets];

Here is what I currently have. I would expect to see 1, 2, 3, etc for the Rank measure. It should reset for each division for every year.

Answer 1

I like this sort of pattern:

WITH 
  SET [AllCountries] AS 
    [Country].[Country].MEMBERS 
  SET [AllProds] AS 
    [Product].[Product].[Product].MEMBERS 
  SET [Top5Prods] AS 
    Generate
    (
      [AllCountries] AS a
     ,{
          (
            a.CurrentMember
           ,[Product].[Product].[All]
          )
        + 
            //The top x prods
            a.CurrentMember
          * 
            TopCount
            (
              [AllProds]
             ,5
             ,[Measures].[Internet Sales Amount]
            )
      }
    ) 
  MEMBER [Product].[Product].[All].[Other Products] AS 
    Aggregate
    (
        [Country].CurrentMember * [Product].[Product].[Product].MEMBERS
      - 
        [Top5Prods]
    ) 
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,Hierarchize(
    {
     [Top5Prods]
    ,[AllCountries] * [Product].[Product].[All].[Other Products]
    }
) ON ROWS
FROM [Adventure Works];

It returns the following:

There is quite an extensive thread here: Top X of Top Y with RestOf member where X and Y are hierarchies from different dimensions