Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Product Sales, Top and BottomCount in the same report

Posted by thomasivarssonmalmo on August 18, 2008

Here is a MDX code snippet that will show you how to combine named set based on TopCount and BottomCount. This type of report is sometimes refered to as "winners and loosers". This means that the report will show the best selling products and the worst for a single year.
It is also easy to use this code in others types of analytics like the products with most and least errors.
I will start this time with showing the final result.

Top and bottomCount

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: