InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 07:07:40 GMT20http://www.sqlservercentral.com/Forums/Topic1409208-17-1.aspxMany thanks.I think it is a good soluton too.Someone advice me that is it works the same...[b]IIF(ISEMPTY([Measures].[Sales Amount] ),NULL,SUM ({ [Company].[All Company].[COMET]},[Measures].[Sales Amount]) ),NON_EMPTY_BEHAVIOR = {[Measures].[Sales Amount]}[/b]Thanks a lot.Sorry but even if i use SSAS 2008 R2 from a long time (5 years) , i am not so good in MDX.i HAVE TO IMPROVE MY knowledge.Tue, 22 Jan 2013 07:23:24 GMTmaretixhttp://www.sqlservercentral.com/Forums/Topic1409208-17-1.aspxI think that something like the following might do what you are after...with member measures.AllSalesAmountOfWholeCompany AS (SUM ( { ( [Company].[All Company].[COMET], [Stores].[Stores].[Stores] ) } ,[Measures].[Sales Amount]) ), NON_EMPTY_BEHAVIOR = 'Sales Amount' select {[Measures].[Sales Amount] , [Measures].[AllSalesAmountOfWholeCompany] } on columns, {[Stores].[Stores].Members} on rows from [Your Cube]Mon, 21 Jan 2013 20:22:32 GMThappycat59http://www.sqlservercentral.com/Forums/Topic1409208-17-1.aspxCiaoMany thanks to you ..It works !!!Thanks again !!!!:-)Mon, 21 Jan 2013 10:39:24 GMTmaretixhttp://www.sqlservercentral.com/Forums/Topic1409208-17-1.aspxThink I've got the iif statement the wrong way around[code="other"]IIF(ISEMPTY([Measures].[Sales Amount] ),NULL,SUM ({ [Company].[All Company].[COMET]},[Measures].[Sales Amount]) ),NON_EMPTY_BEHAVIOR = {[Measures].[Sales Amount]}[/code]ApologiesMackSun, 20 Jan 2013 10:09:11 GMTMackershttp://www.sqlservercentral.com/Forums/Topic1409208-17-1.aspxYou could use a case statement or iif statement. I'd also use the non_empty_behavior clause as well[code="other"]IIF(ISEMPTY([Measures].[Sales Amount] ),SUM ({ [Company].[All Company].[COMET]},[Measures].[Sales Amount]) ,NULL),NON_EMPTY_BEHAVIOR = {[Measures].[Sales Amount]}[/code]MackSun, 20 Jan 2013 08:42:18 GMTMackershttp://www.sqlservercentral.com/Forums/Topic1409208-17-1.aspxHi to all.I use SSAS 2008 R2 and i built a cube.In this cube i have a little trouble with a calculated member i have to do.My new calculate member is named AllSalesAmountOfWholeCompany I need to calculate the total amoun of sales of all company.I'd like to be able my users, to display in the same moment sales amount fo a single store of the company and in the same row the total amount of all company...(A buyer asked me that and i found interesting to do !!!)I built calculate member in MDX and i was able to do that BUT my trouble is to hide the row of stores that has SALES AMOUNT as NULL or 0.....(In the example member BOLOGNA Store...)How can i do that ???? Inside Calculation member definition or where ????I have the same problem in Excel and SQL SERVER MANAGEMENT STUDIO too...I suppose i have to force NULL or 0 my calculated member AllSalesAmountOfWholeCompany, in this way, Excel will display only stores with a Sales Amount > 0.But i tried i was not able,,,i am not a super MDX man ,,,i know DIM STORES --- Sales Amount of a Month ---- AllSalesAmountOfWholeCompany Turin Store ----- 100 --- 120.000Bologna Store ----- NULL ---- 120.000Venice Store ----- 300 ---- 120.000Here is my MDX that it works correctly SUM ( { ( [Company].[All Company].[COMET], [Stores].[Stores].[Stores] ) } ,[Measures].[Sales Amount]) Thanks in advance...Sat, 19 Jan 2013 09:34:56 GMTmaretix