Mondrian: calculating filter totals

5
votes

I have this MDX query:

    with member [Measures].[count] as '[Measures].[NUMBER]'
         ** member [Measures].[total] as 'sum({[RECORD_TYPE.VALUES].[All VALUEs].children})' **
         member [Measures].[% records] as '([Measures].[count] / [Measures].[total])', format_string = "0.0%"
   select {[Measures].[count], [Measures].[% records], [Measures].[total]} ON COLUMNS,
  [RECORD_TYPE.VALUE].[1] ON ROWS
    from [RECORDS]
   where Crossjoin({[CITY].[LONDON]}, {([DATE.DATE].[20171101] : [DATE.DATE].[20171130])})

I have found out that this [total] member only counts filtered records by date (records data between 20171101 and 20171130). If I switch this total to: 

member [Measures].[total] as 'sum({[DATE.DATE].[All DATEs].children})'

I am getting all records in cube, regardless of city.

I would like this total to be the total number of records for [CITY].[LONDON] in cube, no matter the record type. Could you please tell me how do I do this?

1
mdxmondrian

1 Answers

1
votes

I'm not a mondrian expert, but my guess is the following:

  with member [Measures].[count] as '[Measures].[NUMBER]'
         ** member [Measures].[total] as 'sum({[RECORD_TYPE.VALUES].[All VALUEs].children} * [CITY].[LONDON])' **
             member [Measures].[% records] as '([Measures].[count] / [Measures].[total])', format_string = "0.0%"
       select {[Measures].[count], [Measures].[% records], [Measures].[total]} ON COLUMNS,
      [RECORD_TYPE.VALUE].[1] ON ROWS
        from [RECORDS]
       where Crossjoin({[CITY].[LONDON]}, {([DATE.DATE].[20171101] : [DATE.DATE].[20171130])})