Get the top 5 brands of each food subcategory

Question

I would like to take the top 5 selling brands for each subcategory of the category food compared with the yearly income in mdx query using the foodmart database.

To be more specific, we have a dimension called product that contains the product category and the brand name of a product. A product category may contains many sub categories. For example:

{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Blue Medal].[Blue Medal Egg Substitute]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Blue Medal].[Blue Medal Large Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Blue Medal].[Blue Medal Large Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Blue Medal].[Blue Medal Small Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Blue Medal].[Blue Medal Small Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Giant]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Giant].[Giant Egg Substitute]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Giant].[Giant Large Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Giant].[Giant Large Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Giant].[Giant Small Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Giant].[Giant Small Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Jumbo]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Jumbo].[Jumbo Egg Substitute]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Jumbo].[Jumbo Large Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Jumbo].[Jumbo Large Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Jumbo].[Jumbo Small Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Jumbo].[Jumbo Small Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[National]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[National].[National Egg Substitute]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[National].[National Large Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[National].[National Large Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[National].[National Small Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[National].[National Small Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Urban]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Urban].[Urban Egg Substitute]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Urban].[Urban Large Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Urban].[Urban Large Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Urban].[Urban Small Brown Eggs]}
{[Product].[All Products].[Food].[Eggs].[Eggs].[Eggs].[Urban].[Urban Small Eggs]}

In the above example we can see that the subcategory eggs contains many subcategories also that contains the brands.

The final purpose is to show that for the subcategory eggs the top 5 brands for the people that have as yearly income < 10.000 are {the list}. And we want that for each sub-category of category foods using the measure unit sales.

The output results should be like:

salary   |Eggs                              |Meat
          Brand1|Brand2|Brand3|Brand4|Brand5|Brand1|Brand2|Brand3|Brand4|Brand5
&lt 10.000k |Name1 |Name2 |Name3 |Name4 |Name5 |Name1 |Name2 |Name3 |Name4 |Name5
&gt 10.000k |Name1 |Name2 |Name3 |Name4 |Name5 |Name1 |Name2 |Name3 |Name4 |Name5

Any help would be much appreciated.

Answer 1

You need to filter data on the basis of ranks. Take a look at the example below. It is based on adventureworks. In the example for each salary group, I am returning the top three subcategories, for each product category based on their values.

WITH 
MEMBER [Measures].[Internet Sales Amount Rank] AS
RANK( ([Product].[Category].currentmember,[Product].[Subcategory].CurrentMember),
ORDER( ([Product].[Category].currentmember,[Product].[Subcategory].[Subcategory].Members) , [Measures].[Internet Sales Amount], BDESC)
) 

select 
non empty
([Product].[Category].[Category],filter([Product].[Subcategory].[Subcategory],[Measures].[Internet Sales Amount Rank]<4))
on columns,
non empty 
([Customer].[Yearly Income].[Yearly Income],[Measures].[Internet Sales Amount])
on rows 
from [Adventure Works]

Result

Answer 2

This is your script. There are a lot of braces which is signalling a tuple to Mondrian:

WITH 
MEMBER [Measures].[Top5] AS 
RANK ( 
  (
   [Product].[Product Category].currentmember
  ,[Product].[Brand Name].CurrentMember
  )
  , ORDER( 
      (
         [Product].[Product Category].currentmember
        ,[Product].[Brand Name].[Brand Name].Members
      ) 
      , [Measures].[Store Sales]
      , BDESC
    ) 
  ) 
SELECT
non empty 
(
  [Product].[Product Category].[Product Category]
 ,filter(
    [Product].[Brand Name].[Brand Name]
  , [Measures].[Top5]<6
   )
) on columns, 
non empty (
   [Yearly_Income].[Yearly Income],
   [Measures].[Store Sales]
) on rows 
from [projetDW];

Maybe try using the CROSSJOIN function to produce your sets of tuples:

WITH 
MEMBER [Measures].[Top5] AS 
RANK ( 
  CROSSJOIN(
   { [Product].[Product Category].currentmember }
  ,{ [Product].[Brand Name].CurrentMember }
  )
  , ORDER( 
      CROSSJOIN(
         { [Product].[Product Category].currentmember }
        ,[Product].[Brand Name].[Brand Name].Members
      ) 
      , [Measures].[Store Sales]
      , BDESC
    ) 
  ) 
SELECT
non empty 
CROSSJOIN(
  [Product].[Product Category].[Product Category].MEMBERS
 ,FILTER(
     [Product].[Brand Name].[Brand Name]
   , [Measures].[Top5]<6
  )
) on columns, 
non empty 
(
   [Yearly_Income].[Yearly Income],
   [Measures].[Store Sales]
) on rows 
from [projetDW];