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?

whytheq · Accepted Answer · 2015-07-13T08:59:25.517

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:

enter image description here

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:

enter image description here

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:

enter image description here

Ok I've been looking at this solution but not having much luck. I gather that for this to work I would need to create a measure calle "[Internet Sales diff]" correct? — John, Jul 14 '15 at 13:58
exactly: you should be able to add a WITH clause into the mdx editor in SSRS - I assume that is what you're using. — whytheq, Jul 14 '15 at 14:06
@whytheq I have a similar problem with MDX, can you share ur email id for me to share the question as i'm not able to post my question here, pls ? — singhswat, Oct 30 '17 at 23:11
@singhswat - try adding it as a SO question - then post the url to the question here: I will have a look at it then (also there are a couple of other users who will have visibility of it and can also try to help) — whytheq, Oct 31 '17 at 12:10
@whytheq It's been many years since this answer but it's just what I was looking for. Thank you very much! — virgiliogm, Jul 27 '23 at 13:44
@virgiliogm Thanks! `MDX` is still a thing of beauty but it is definitely on the endangered list these days! — whytheq, Jul 27 '23 at 14:32

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

1 Answers1