ssas - 对 Select 语句的特定列求和

Question

我对 SSAS 和 MDX 非常陌生，并试图了解它的概念。我需要帮助。

我有一个预订事实表，我想按市场获取特定预订年份和出发年份的乘客数量，并且对于每个出发年份，我想要一个总列。但无法弄清楚如何将这些列聚合为一。

这是我现在的代码:

SELECT  {
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year].&[2011] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year].&[2012] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year].&[2012] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year].&[2013] ))
        }
        ON 0,  
NON EMPTY [Fact Lead Pax Report].[Mc Major].MEMBERS
ON 1
FROM [Lead Pax Report]
WHERE { [Fact Lead Pax Report].[Res Status].&[A] }

这是我的结果表，我想添加黄色标记所在的总列:

Answer 1

添加另一列来计算特定年份的所有离开怎么样？

例如:

WITH SET [ESC TOURS BY MARKET] AS 
Filter( 
  [Fact Lead Pax Report].[Mc Major].[Mc Major], 
  ([Fact Lead Pax Report].[Mc Major].currentMember.name <> 'AIR') AND ([Fact Lead Pax Report].[Mc Major].currentMember.name <> 'DEFAULT')
)
SELECT  {
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year].&[2011] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year].&[2012] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year].&[2012] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year].&[2013] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year] ))
        }
        ON 0,  
[ESC TOURS BY MARKET]
ON 1
FROM [Lead Pax Report]
WHERE { [Fact Lead Pax Report].[Res Status].&[A] }