Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SSAS 2008 R2 - Problems with total of a calculated measure in Excel Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, November 06, 2013 4:02 PM
 Valued Member Group: General Forum Members Last Login: Sunday, March 02, 2014 11:24 AM Points: 66, Visits: 124
 Hi to all.I try to explain my problem.I use SSAS 2008 R2 and i have a Multidimensional Cube called SALES.In this cube i have dimensions and measures.Now i try to simplify the design of it and explain what is my question...I have a dimension STORES wtih all stores of my companyI have a dimension CALENDAR with all time periods I have a dimension PRODUCT CATEGORY with 3 levels (Parent Child Dimension)1) Level 12) Level 23) Level 3I have a measure called SALES AMOUNT (It sum all sales amount) depending on a selection .I created a new calculated measure that we can call 'SALES AMOUNT CUSTOMED'This measure need to sum SALES AMOUNT depending on a selection of levels of PRODUCT CATEGORY dimension...and depending on that i need to subtract tha sales amount of a certain member of Dimension STORES.I post here the MDX Expression of SALES AMOUNT CUSTOMED' calculated measureCASE LEFT([Product Category].[Product Category].CURRENTMEMBER.Name,1) WHEN 'E' THEN ([Measures].[SALES AMOUNT] - [Measures].[SALES AMOUNT ONLY STORE CO16]) WHEN 'M' THEN ([Measures].[SALES AMOUNT] - [Measures].[SALES AMOUNT ONLY STORE CO11]) WHEN 'X' THEN ([Measures].[SALES AMOUNT]) WHEN 'I' THEN ([Measures].[SALES AMOUNT]) WHEN 'A' THEN ([Measures].[SALES AMOUNT]) WHEN 'Z' THEN ([Measures].[SALES AMOUNT]) ELSE ([Measures].[SALES AMOUNT] - [Measures].[SALES AMOUNT ONLY STORE CO16] - [Measures].[SALES AMOUNT ONLY STORE CO11])ENDThis measure works fine !!!! BUT MY PROBLEM is when i browse my cube with Excel (2010 or 2013 no differences)Excel does not calculate GRAND TOTAL correctly about my SALES AMOUNT CUSTOMED' calculated measureHere is what happen...PRODUCT CATEGORY ---- SALES AMOUNT ---- SALES AMOUNT CUSTOMEDLEVEL 1 - LEVEL 2 - LEVEL 3E EE EE04 100 90E EE EE05 200 100E EE EE06 300 200E EE EE07 400 300 ==== ====TOTAL --E-- 1000 690 (Sub Total is correct !!!)M MM MM01 10 9M MM MM03 70 60 ==== ====TOTAL --M-- 80 69 (Sub Total is correct !!!)GRAND TOTAL 1080 1080 [b](IT IS WRONG WHY !!!)[/b] (1000 + 80 correct) (Grand Total of Calculated Measure It should be 690 + 69 = 759 )Why Excel does that ???It seems it does error calculating wrong Total about only calculated measure ...How can i solve that problem ???With Excel or with a different MDX Expression ???????????Thanks in advance for your replies...Regards.ps. TO EXPLAIN better i could not to post my excel...
Post #1512027
 Posted Thursday, November 07, 2013 8:37 AM
 Old Hand Group: General Forum Members Last Login: Monday, February 24, 2014 3:07 AM Points: 390, Visits: 695
 What happens when you browse in BIDS/SSDT?It could have something to do with visual totals. Are you browsing as an admin or under a restricted role? I'm on LinkedIn
Post #1512317
 Posted Thursday, November 07, 2013 4:17 PM
 Valued Member Group: General Forum Members Last Login: Sunday, March 02, 2014 11:24 AM Points: 66, Visits: 124
 Hi thanks for your kind reply.I have all priviligies and i have the same problem with Excel and SQLSRVER MANAGEMENT STUDIO.I read it is a common problem...visualizing totals with calculated members.The solution i think it is study SCOPE ASSIGNMENTS and try something like this...I do not know how to...but if i will be able to ...i will post solution.Regards
Post #1512488

 Permissions