Answered by:
How can I do SSAS Calculation in conditionally

Hi,
I have a sales(SalesID, GoodsType, GrossSale, AverageSale). I was to write two MDX expression in MS Visual Studio SSAS cube's Calculation tab to calculate Sum of GrossSale and Average of GrossSale where GoodsType is "Food". If someone please write the MDX expression for me?
I wrote this expression for sum but it doesn't work!
CREATE MEMBER CURRENTCUBE.[Measures].Sales
AS SUM([Measures].[Gross Sale], [Measures].[Datatype] = "2"),
VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast' ;
........................
I have tried in this way also
CREATE MEMBER CURRENTCUBE.[Measures].Sales
AS SUM([Measures].[Datatype].&[6], [Measures].[Gross Sale]),
VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast' ;
I should elaborate bit more, please check bellow,
I have a data table. Where GrossSale and Datatype are columns. Say in GrossSale I have (100, 200, 400, 130, 350) and in Datatype I have (5,2,6,8,2) respectively. Now I want Sum of GrossSale where I have Datatype 2. Then I have GrossSale = 550, that is 200+350. Because for Datatype 2, I have 200 and 350.
If someone can help me please? Moved by Mike YinMicrosoft contingent staff, Moderator Monday, October 21, 2013 1:43 AM more appropriate forum
Question
Answers

Hi Tuhin ,
It is look like you are using the AVG/Sum funnctio as SUM(measure,measue) .
It won't work this way . The Sum function have to get a set and then a measure .
Try to create a dimension with the 'Datatype' attribute so you'll be able to use it in the sum function .
Let's say the new dimension will be called 'DIM', the attribute 'DatatypeAtt', and his keycolumn (attribute properties) will be the column Datatype.
It will look like the next :CREATE MEMBER CURRENTCUBE.[Measures].[Sales]
AS SUM({[DIM].[DatatypeAtt].&[2]}, [Measures].[Sales]),
VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast';Hope it helps :)
Regards, David .
 Proposed as answer by Elvis LongMicrosoft contingent staff, Moderator Thursday, October 31, 2013 1:54 AM
 Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Monday, November 04, 2013 2:35 AM
All replies

Hi,
I have a sales(SalesID, GoodsType, GrossSale, AverageSale). I was to write two MDX expression in MS Visual Studio SSAS cube's Calculation tab to calculate Sum of GrossSale and Average of GrossSale where GoodsType is "Food". If someone please write the MDX expression for me?
I wrote this expression for sum but it doesn't work!
CREATE MEMBER CURRENTCUBE.[Measures].Sales
AS SUM([Measures].[Gross Sale], [Measures].[Datatype] = "2"),
VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast' ;
........................
I have tried in this way also
CREATE MEMBER CURRENTCUBE.[Measures].Sales
AS SUM([Measures].[Datatype].&[6], [Measures].[Gross Sale]),
VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast' ;
I should elaborate bit more, please check bellow,
I have a data table. Where GrossSale and Datatype are columns. Say in GrossSale I have (100, 200, 400, 130, 350) and in Datatype I have (5,2,6,8,2) respectively. Now I want Sum of GrossSale where I have Datatype 2. Then I have GrossSale = 550, that is 200+350. Because for Datatype 2, I have 200 and 350.
If someone can help me please? Merged by Elvis LongMicrosoft contingent staff, Moderator Monday, October 21, 2013 8:48 AM duplicate

Hi Tuhin ,
It is look like you are using the AVG/Sum funnctio as SUM(measure,measue) .
It won't work this way . The Sum function have to get a set and then a measure .
Try to create a dimension with the 'Datatype' attribute so you'll be able to use it in the sum function .
Let's say the new dimension will be called 'DIM', the attribute 'DatatypeAtt', and his keycolumn (attribute properties) will be the column Datatype.
It will look like the next :CREATE MEMBER CURRENTCUBE.[Measures].[Sales]
AS SUM({[DIM].[DatatypeAtt].&[2]}, [Measures].[Sales]),
VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast';Hope it helps :)
Regards, David .
 Proposed as answer by Elvis LongMicrosoft contingent staff, Moderator Thursday, October 31, 2013 1:54 AM
 Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Monday, November 04, 2013 2:35 AM

Hi, you may want to look at the following links:
http://www.sqlserverdatamining.com/OLAPPapers/MDX%20Performance%20Hintsv2.htm
Regards, Leo