MDX Help - Should be easy

I want to have a calculation that rolls up monthly closed sales and displays the total month volume no matter what dimension levels have been added.

Example:

Month Store # Sales Total Monthly Sales

April 1 10,000 50,000
April 2 10,000 50,000
April 3 30,000 50,000
May 1 20,000 70,000
May 2 20,000 70,000
May 3 30,000 70,000

This could be across multiple dimensions (ie, I could add product type, business day number, etc) and I would still want the total monthly sales to be the sum of all sales for the month for the entire company.

I have tried several things, but I do not know enough about MDX to know where to look.

Thanks for your help!!

Bob

I think this is what you are looking for:

with member [Measures].[Total Month Sales] as '([Time].currentmember, [Store].[All Stores], [Measures].[Unit Sales])'

select
{[Measures].[Unit Sales],[Measures].[Total Month Sales]} on columns,
{nonemptycrossjoin({descendants([Time].[1997],[Time].[Month])},{[Store].[Store Name].members})} on rows
from Sales

The trick is to add all the 'All' members to [Measures].[Total Month Sales]. For instance, if you have a product dimension, it should be

'([Time].currentmember, [Store].[All Stores], [Product].[All Product], [Measures].[Unit Sales])'

Hope this helps,

Santi

|||

AWESOME! Thanks for your quick answer!!

BobP