Printed 2017/08/20 10:02AM

MDX Aggregate Named Set Than Others


In this quick post I wanted to provide some code that's been useful to me recently.  This MDX statement creates a named set of the Top 20 products and then aggregates on a selected measure.  Commonly though you may want to return an aggregation on those Top 20 products but optionally have an Others member that displays the aggregated sales for all other products not included in the set. 

My explanation might be more confusing than my code so here it is!  You can run it against the Adventure Works cube to simulate the results.

Set [Top20Products] AS 
(TOPCOUNT({ ORDER( HIERARCHIZE( {[Product].[Product].[All].Children}), ([Measures].[Internet Sales Amount]), BDESC ) }, 20))  
MEMBER [Product].[Product].[Other Products] AS  
(AGGREGATE({EXCEPT([Product].[Product].Members, [Product].[Product].[Top20Products])}))  

Select [Measures].[Internet Sales Amount] on 0,  
{[Top20Products],[Product].[Product].[Other Products]}on 1  
From [Adventure Works]



Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.