Calculating subtotals using MDX

Not related to a specific OLAP tool. (Includes forum policies and rules).
Post Reply
bancika
Posts: 1
Joined: Fri Mar 26, 2010 12:03 pm
OLAP Product: mondrian
Version: 3.1
Excel Version: 2007

Calculating subtotals using MDX

Post by bancika »

Hi,

I have the following query:

Code: Select all

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: Select all

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)

Image

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

Image

Thanks in advance,
Bane
Edit/Delete Message
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: Calculating subtotals using MDX

Post by Marcus Scherer »

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
Attachments
result.gif
result.gif (5.94 KiB) Viewed 12428 times
noezavala
Posts: 1
Joined: Fri Jun 24, 2011 6:22 pm
OLAP Product: SSAS 2008
Version: SSAS 2008
Excel Version: 10.0

Re: Calculating subtotals using MDX

Post by noezavala »

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
Post Reply