Aggregate Facts bases on selected Dimension members

Question

How to add a Total Column like in below image.

the Query Structure is as below

WITH
    MEMBER [Measure].[Total Responses] AS [Measures].[Responses]

    MEMBER [Measure].[Option 1] as CoalesceEmpty([Measures].[Option1],0)
    MEMBER [Measure].[Option 2] as CoalesceEmpty([Measures].[Option2],0)
SELECT
{
    //Add Total Here for Below Dimension Members
    [Date].[Month].&[2018]&[2018-Q4]&[2018-09], //say Month 1
    [Date].[Month].&[2018]&[2018-Q4]&[2018-10], //say Month 2
    [Date].[Month].&[2019]&[2019-Q1]&[2019-01]  //say Month 5 
} ON COLUMNS,
NON EMPTY
{
    [Measure].[Total Responses],
    [Measure].[Option 1],
    [Measure].[Option 2]
} ON ROWS
FROM [Cube]

I am able to get the desired result by adding a new Aggregate Member as follows MEMBER [Date].[Month].[AllMessages] AS AGGREGATE({[Date].[Month].&[2018]&[2018-Q4]&[2018-12],[Date].[Month].&[2019]&[2019-Q1]&[2019-01]}) and using [Date].[Month].[AllMessages], in the Columns set — Kritul Rathod

Kritul Rathod Kritul Rathod · Accepted Answer · 2019-01-26T20:33:04

The Edits are marked with ** in below query

WITH
  MEMBER [Measure].[Total Responses] AS [Measures].[Responses]

  **MEMBER [Date].[Month].[AllMessages] AS AGGREGATE({[Date].[Month].&[2018]&[2018-Q4]&[2018-12],[Date].[Month].&[2019]&[2019-Q1]&[2019-01]})

  MEMBER [Measure].[Option 1] as CoalesceEmpty([Measures].[Option1],0)
  MEMBER [Measure].[Option 2] as CoalesceEmpty([Measures].[Option2],0)

SELECT
{
  **[Date].[Month].[AllMessages],
  [Date].[Month].&[2018]&[2018-Q4]&[2018-12],
  [Date].[Month].&[2019]&[2019-Q1]&[2019-01]
} ON COLUMNS,
NON EMPTY
{
  [Measure].[Total Responses],
  [Measure].[Option 1],
  [Measure].[Option 2]
} ON ROWS
FROM
  [Cube]

Aggregate Facts bases on selected Dimension members

1 Answers