TM1 Forum

Hi,

I have the following query:
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
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)



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



Thanks in advance,
Bane
Edit/Delete Message

Hi Bane,
if you are referring to the mondrian example, don't ignore the already existing parent levels (e.g. "All Marital Status"):

your statement is:

select {[Measures].[Unit Sales]} ON COLUMNS,
{Crossjoin([Gender].[Gender].Members, [Marital Status].Members)} ON ROWS
from [Sales]

Try out what happens if you use [Gender].Members instead of [Gender].[Gender].Members also.

Ciao,
Marcus

Use this:

WITH MEMBER [Measures].[Unit Sales Subtotal Gender] AS
([Gender].CurrentMember, [Marital Status].CurrentMember.Parent, [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