CODEWITHSUNDEEP
mdxcubeolap-cube
John
John

Reputation: 277

MDX exclude filter in total calculation

The following query will return results for Cycling Cap product now please ignore the actual calculation I’m just using this as an example. What I’m interested in is that I get the results based on the filtering of data using [Product].[Model Name].&[Cycling Cap] see below.

WITH 
  MEMBER [Measures].[Unit Costing] AS 
  (
  ([Measures].[Total Product Cost] / [Measures].[Unit Price])*    [Measures].[Internet Sales Count] 
    ), format_string = '#,###,###,##0' 

SELECT NON EMPTY { 
  [Measures].[Sales Amount]
, [Measures].[Total Product Cost]
, [Measures].[Unit Price]
, [Measures].[Internet Sales Count] 
, [Measures].[Unit Costing]
} ON COLUMNS, NON EMPTY 
{ 
 {
[Order Date].[Calendar].[Calendar Year].&[2008] * 
[Order Date].[Calendar Month].[Calendar Month] *
[Product].[Model Name].&[Cycling Cap]} -- if this is removed second set of results returned
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [Adventure Works Cube]

What I get retuned is 

Sales Amount    Total Product Cost  Unit Price  Internet Sales Count    Unit Costing
2008    August      Cycling Cap 2876.8   2215.136   2876.8  320      246
2008    February    Cycling Cap 5915.42  4554.8734  5915.42 658      507
2008    July        Cycling Cap 2400.33  1848.2541  2400.33 267      206
2008    March       Cycling Cap 5483.9   4222.603   5483.9  610      470

Now if I take out [Product].[Model Name].&[Cycling Cap] the reults are

Sales Amount    Total Product Cost  Unit Price  Internet Sales Count    Unit Costing
2008    August      4113748.61980021    2419644.0697        4113748.61980021    8596    5,056
2008    February    8388478.68060071    4930341.31220008    8388478.68060071    17236   10,130
2008    July        4154919.58680019    2445288.84530001    4154919.58680019    8550    5,032
2008    March       8358856.30390072    4919934.72500009    8358856.30390072    17067   10,045

So is it possible to have the Unit Cost return the unfiltered value of 5,056 for August instead of 246 which is what I’m currently getting.

Upvotes: 0

Views: 1017

Answers (1)

whytheq
whytheq

Reputation: 35557

If you tuple each of the measures with the ALL member in the calculation then it should force it to be against ALL rather than Cycling Cap:

WITH 
  MEMBER [Measures].[Unit Costing] AS 
        (
          [Measures].[Total Product Cost]
         ,[Product].[Model Name].[All]
        )
      / 
        (
          [Measures].[Unit Price]
         ,[Product].[Model Name].[All]
        )
    * 
      (
        [Measures].[Internet Sales Count]
       ,[Product].[Model Name].[All]
      ) 
   ,format_string = '#,###,###,##0' 
SELECT 
  NON EMPTY 
    {
      [Measures].[Sales Amount]
     ,[Measures].[Total Product Cost]
     ,[Measures].[Unit Price]
     ,[Measures].[Internet Sales Count]
     ,[Measures].[Unit Costing]
    } ON COLUMNS
 ,NON EMPTY 
    -- if this is removed second set of results returned
    {
        [Order Date].[Calendar].[Calendar Year].&[2008]*
        [Order Date].[Calendar Month].[Calendar Month]*
        [Product].[Model Name].&[Cycling Cap]
    } ON ROWS
FROM [Adventure Works];

Upvotes: 1

Related Questions