Here is my MDX query
WITH
SET [Top5Cites] AS
' TOPCOUNT( [Store].[Store City].Members , 5, [Measures].[Unit
Sales] )'
SET [AllCities] AS
' FILTER ( { [Store].[Store City].Members} ,
[Measures].[Unit Sales] > 0 )'
SET [NotTop5] AS
' EXCEPT( [AllCities] , [Top5Cites] ) '
MEMBER [Measures].[NotTop5_Sum] AS
' Sum ([NotTop5] , [Measures].[Unit Sales] )'
--/*
SELECT { [Measures].[Unit Sales] } on COLUMNS
, [Top5Cites] ON ROWS
FROM Sales
--*/
/*
SELECT { [Measures].[NotTop5_Sum] } on COLUMNS
FROM Sales
*/
I want the results to look something like
City Sales
Salem 41,580
Tacoma 35,257
PDX 26,079
LA 25,663
SD 25,635
OTHERS 112,559
WITH
SET Top5Cities AS
' TOPCOUNT( [Store].[Store City].Members , 5,
[Measures].[Unit Sales] )'
MEMBER Store.Other AS
' (Store.DefaultMember, [Unit Sales]) -
Sum([Top5Cities], [Unit Sales]) '
SELECT { [Measures].[Unit Sales] } on COLUMNS,
{ [Top5Cities], {Store.Other} } ON ROWS
FROM Sales
p.s. I wish everyone expressed their problems in foodmart terms.
tom @ the domain below
www.tomchester.net
"Jon Webber" <jwe...@noSpam.com> wrote in message
news:#kfBktkF...@TK2MSFTNGP10.phx.gbl...
"Tom Chester" <public...@tomchester.net> wrote in message
news:3lRua.270$6I2....@news.uswest.net...
WITH
SET Top5Cities AS
' TOPCOUNT( [Store].[Store City].Members , 5,
[Measures].[Unit Sales] )'
MEMBER Store.Other AS
' (Store.DefaultMember, [Unit Sales]) -
Sum([Top5Cities], [Unit Sales]) '
MEMBER Measures.[%Total] AS
' (Store.CurrentMember, [Unit Sales]) /
(Store.DefaultMember, [Unit Sales]) ' ,
Format_String = '0.0%'
SELECT { [Measures].[Unit Sales], Measures.[%Total] } ON COLUMNS,
{ [Top5Cities], {Store.Other} } ON ROWS
FROM Sales
tom @ the domain below
www.tomchester.net
"Jon Webber" <jwe...@noSpam.com> wrote in message
news:OK8X2RoF...@TK2MSFTNGP12.phx.gbl...