MDX How do you create a variance and variance % in a report

Question

Using AdventureWorksDW2008R I have the following DataSet

SELECT NON EMPTY { 
[Measures].[Sales Amount], [Measures].[Total Product Cost], [Measures].[Internet Sales Count] 
} ON COLUMNS, NON EMPTY 
{ 
([Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) 
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [Adventure Works Cube]

Resutls are:

         Sales Amount          Total Product Cost       Internet Sales Count
2005     4342674.0296          2562584.6235             8949
2008     25016003.1911002      14715208.9522001         51449

Is there a way to calculate the variance of each in the report?

For example the Variance of Internet Sales Count would be: 51449 – 8949 = 42500

And the % variance would be 42500/51449 = 83%

I know I can use the following to get the Sum:

=Sum(Fields!Internet_Sales_Count.Value, "DataSet1")

Is there a way to get the 2008 value and subtract the 2005 value?

Answer 1

Here is one possibility:

WITH 
  MEMBER [Measures].[Internet Sales diff] AS 
      (
        [Delivery Date].[Calendar Year].CurrentMember
       ,[Measures].[Internet Sales Amount]
      )
    - 
      (
        [Delivery Date].[Calendar Year].CurrentMember.Lag(1)
       ,[Measures].[Internet Sales Amount]
      ), format_string = '#,###,###,##0.00' 
SELECT 
  NON EMPTY 
    {
      [Measures].[Sales Amount]
     ,[Measures].[Total Product Cost]
     ,[Measures].[Internet Sales Amount]
     ,[Measures].[Internet Sales diff]
    } ON COLUMNS
 ,NON EMPTY 
    {[Delivery Date].[Calendar Year].[Calendar Year].ALLMEMBERS}
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM [Adventure Works];

The result of the above is the following:

A percentage measure could then be added like this:

WITH 
  MEMBER [Measures].[Internet Sales diff] AS 
      (
        [Delivery Date].[Calendar Year].CurrentMember
       ,[Measures].[Internet Sales Amount]
      )
    - 
      (
        [Delivery Date].[Calendar Year].CurrentMember.Lag(1)
       ,[Measures].[Internet Sales Amount]
      ) 
   ,format_string = '#,###,###,##0.00' 
  MEMBER [Measures].[Internet Sales diff %] AS 
    IIF
    (
      [Measures].[Internet Sales Amount] = 0
     ,null
     ,
        [Measures].[Internet Sales diff]
      / 
        (
          [Delivery Date].[Calendar Year].CurrentMember.Lag(1)
         ,[Measures].[Internet Sales Amount]
        )
    ) 
   ,format_string = '#,###,###,##0.00%' 
SELECT 
  NON EMPTY 
    {
      [Measures].[Sales Amount]
     ,[Measures].[Total Product Cost]
     ,[Measures].[Internet Sales Amount]
     ,[Measures].[Internet Sales diff]
     ,[Measures].[Internet Sales diff %]
    } ON COLUMNS
 ,NON EMPTY 
    {[Delivery Date].[Calendar Year].[Calendar Year].ALLMEMBERS}
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM [Adventure Works];

Results in this:

---

Here is a better approach using the parallelperiod function:

WITH 
  MEMBER [Measures].[Internet Sales PrevYr] AS 
    IIF
    (
      [Measures].[Internet Sales Amount] = 0
     ,null
     ,(
        [Measures].[Internet Sales Amount]
       ,ParallelPeriod
        (
          [Delivery Date].[Calendar Year].[Calendar Year]
         ,1
         ,[Delivery Date].[Calendar Year].CurrentMember
        )
      )
    ) 
   ,format_string = '$#,###,###,##0.00' 
  MEMBER [Measures].[Internet Sales diff] AS 
    IIF
    (
      [Measures].[Internet Sales Amount] = 0
     ,null
     ,
      [Measures].[Internet Sales Amount] - [Measures].[Internet Sales PrevYr]
    ) 
   ,format_string = '$#,###,###,##0.00' 
  MEMBER [Measures].[Internet Sales diff %] AS 
    IIF
    (
      [Measures].[Internet Sales PrevYr] = 0
     ,null
     ,
      [Measures].[Internet Sales diff] / [Measures].[Internet Sales PrevYr]
    ) 
   ,format_string = '#,###,###,##0.00%' 
SELECT 
  NON EMPTY 
    {
      [Measures].[Internet Sales Amount]
     ,[Measures].[Internet Sales PrevYr]
     ,[Measures].[Internet Sales diff]
     ,[Measures].[Internet Sales diff %]
    } ON COLUMNS
 ,NON EMPTY 
    {[Delivery Date].[Calendar Year].[Calendar Year].MEMBERS} ON ROWS
FROM [Adventure Works];

Results: