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