Returning Top 20% of Products based on Sales

• Michael Torsson

  SSC Eights!

  Points: 881

  More actions

  December 3, 2008 at 3:00 am

  #202461

  Hi

  As the subject says I am interested in populating a SET with the 20% best selling products.

  I tried the TOPPERCENT, but then I get the cumulative 20%.

  I pretty new to MDX and need some guidence:).

  Thanks Michael

• Oliver Bradonjic

  Valued Member

  Points: 62

  More actions

  December 3, 2008 at 4:21 am

  #906729

  If you new in MDX you need this book

  http://www.amazon.com/MDX-Solutions-Microsoft-Analysis-Services/dp/0471748080/ref=sr_1_1?ie=UTF8&s=books&qid=1228303173&sr=1-1

  WITH

  MEMBER [Measures].[Top Product in Children] AS

  TopCount (

  [Product].[ByCategory].CurrentMember.Children,

  1,

  [Measures].[Unit Sales]

  ).Item(0).Item(0).Name

  MEMBER [Measures].[Top Product’s Units] AS

  ( [Measures].[Unit Sales],

  TopCount (

  [Product].[ByCategory].CurrentMember.Children,

  1,

  [Measures].[Unit Sales]

  ).Item(0) // in Essbase, need .Item(0).Item(0)

  )

  SELECT

  CrossJoin (

  { [Time].[YQMD].Children }.

  { [Measures].[Top Product in Children],

  [Measures].[Top Product’s Units]

  }

  ) on columns,

  { [Customer].[Customer].[Region].Members } on rows

  FROM Sales

• Michael Torsson

  SSC Eights!

  Points: 881

  More actions

  December 10, 2008 at 4:52 am

  #910218

  Hi

  I got the book 🙂

  My understanding in MDX i still a little vague;)

  I solved the issue with Order and Topcount.

  Thanks

  Michael

• Oliver Bradonjic

  Valued Member

  Points: 62

  More actions

  December 10, 2008 at 5:39 am

  #910246

  😎