Sum Specific Columns for Select Statement

mdx, ssas

Solution

What about adding another column to count all departures for specific year?

For example:

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] }

Problem

I'm very new to SSAS and MDX and trying to get the concept of it. I need help please. I have a booking fact table and I want to get the number of passengers by market for specific booking year and departure year and for each departure year I want a total columns. But can't figure out how to aggregate these columns to one. Here is my code right now: ``` 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] } ``` And here s my result table, I want to add the total columns where the yellow marker is:

Original source