Thomas.I Microsoft BI & Analytics

I have the top 10 product sales for 2003 as the first ten members followed by the bottom 10 product sales. On the columns I have Internet Sales Amount for 2003 , the market share for each product and finally the ranking of these products over all sold products in 2003.

Here is the code to produce this result. I am sure that there are many other ways to do the same. This time I have checked the query in MDX Studio and the number of Storage Engine queries was 7.

WITH Member Measures.[Market Share] As
(Measures.[Internet Sales Amount],[Product].[Product Categories].CurrentMember)/
(Measures.[Internet Sales Amount], [Product].[Product Categories].[All Products]), Format_String = "0.0#%"

Set Top10Products2003 as TopCount([Product].[Product Categories].[Product].members,10,(Measures.[Internet Sales Amount],[Date].[Calendar Year].&[2003]))

Set Bottom10Products2003 as
BottomCount(NonEmpty([Product].[Product Categories].[Product].members,([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2003])),10,
(Measures.[Internet Sales Amount],[Date].[Calendar Year].&[2003]))

Set Top10Bottom10Products as Union(Top10Products2003,Bottom10Products2003)

Set AllSoldProducts2003 as NonEmpty([Product].[Product Categories].[Product].members,([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2003]))

Member Measures.[RankTopBotton10Products] as
RANK([Product].[Product Categories].CurrentMember,  AllSoldProducts2003,[Measures].[Internet Sales Amount])

Select {Measures.[Internet Sales Amount],Measures.[Market Share],Measures.[RankTopBotton10Products]} On 0,
Order(Top10Bottom10Products,Measures.[Internet Sales Amount],BDESC) On 1
From [Adventure Works]
Where([Date].[Calendar Year].&[2003]);

A few comments regaring this code. The BottomCount function needs the help of the NonEmpty function to not pick the products with null sales in 2003.  The AllSoldProducts2003 is used to rank these 20 products over all sold products. I have also checked that the number of sold products was 133 in 2003. The ranking of products is tied to Internet Sales Amount but it will also work with the market share calculated measure.

You should compare the amount of MDX code required to build this report with a SQL based solution.