mdx grand total percentage calculation

1
votes

I have a MDX query:

select NON EMPTY [TYPE].[All TYPEs].Children ON COLUMNS,
  NON EMPTY [CITY].[All CITYs].Children ON ROWS
from [DW_CUBE]
where [REGION].[MADRID]

This query is showing the number of records with specific types aggregated by city, for a specific region (Madrid in this case).

CITY      TYPE A    TYPE B
MADRID      234       234
COLMENAR    123       234
TRES CANTOS 324     12312

I need to show the values as percentage of total records in the cube (i.e. the sum of all values displayed in the query). For example: 

CITY        TYPE A  TYPE B
MADRID      1,74%   1,74%
COLMENAR    0,91%   1,74%
TRES CANTOS 2,41%   91,46%

Can I do this using MDX functions? Any help would be appreciated. I am using Mondrian as engine.

Thanks

Rafael

2
aggregatemdxpercentagemondrian

2 Answers

4
votes

In Analysis Services, I would use

with member [Measures].[Percent] as
            [Measures].DefaultMember
            /
            (Measures.DefaultMember, [TYPE].[All TYPEs].[All], [CITY].[All CITYs].[All])
            ,format_string = '0.00%'
select NON EMPTY [TYPE].[All TYPEs].Children ON COLUMNS,
  NON EMPTY [CITY].[All CITYs].Children ON ROWS
from [DW_CUBE]
where ([REGION].[MADRID], [Measures].[Percent])

I am not sure if this works in Mondrian. I am guessing the names of the All members of [All TYPEs] and [All CITYs] here. I also see from your query that you use the default measure of the cube (as you did not state a measure in the whole query). If that should not be the case, just replace [Measures].DefaultMember with the measure that you want to use as the base for the percent calculation.

1
votes
(
    Axis(1).Item(1).Hierarchy.CurrentMember
   ,[Measures].[task]
  )
/ 
  Sum
  (
    Axis(1)
   ,[Measures].[task]
  )