Hi,

I have the following query:

Code:
SELECT
 { { [Measures].[Unit Sales]} } ON COLUMNS,
 { CrossJoin([Gender].[Gender].Members, [Marital Status].[Marital Status].Members) } ON ROWS
FROM Sales
It returns data grouped by gender, then by marital status. How can I calculate me Unit Sales total but for Gender?

I tried with something like this

Code:
WITH MEMBER [Measures].[Unit Sales Subtotal Gender] AS
SUM({[Gender].CurrentMember}, [Measures].[Unit Sales])
SELECT
 { { [Measures].[Unit Sales],  [Measures].[Unit Sales Subtotal Gender]} } ON COLUMNS,
 { CrossJoin([Gender].[Gender].Members, [Marital Status].[Marital Status].Members) } ON ROWS
FROM Sales
but it doesn't work, although I'm using [Gender].CurrentMember I still get the same numbers broken by marital status. Here's the result (through JPivot)

http://forums.databasejournal.com/im.../2010/03/1.png

What I want to have is something like this (values circled in red are ones I want to calculate):

http://forums.databasejournal.com/im.../2010/03/2.png

Thanks in advance,
Bane