MDX - Top 10 and Others

WITH

SET [top 10] AS

TopCount
(

[Product].[Product Name].[Product Name].MEMBERS
,10

,[Measures].[Spend]

)

// MEMBER [Product].[Product Name].[Others] AS

// Aggregate([Product].[Product Name].[Product Name].MEMBERS - [top 10])

// OR



MEMBER [Product].[Product Name].[Others] AS

Aggregate(EXCEPT([Product].[Product Name].[Product Name].MEMBERS,[top 10]))





SET [Top 10 AND rollup] AS

{

[top 10]

,[Product].[Product Name].[Others]

}

SELECT

{[Measures].[Spend]

,[Measures].[Spend Ratio Over Product]

} ON COLUMNS
,{[Top 10 AND rollup]} ON ROWS [FactPurchase]

FROM