Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSAS 2008 R2 - Problems with total of a calculated measure in Excel Expand / Collapse
Author
Message
Posted Wednesday, November 6, 2013 4:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 12, 2014 2:30 PM
Points: 83, Visits: 177
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 company
I have a dimension CALENDAR with all time periods
I have a dimension PRODUCT CATEGORY with 3 levels (Parent Child Dimension)
1) Level 1
2) Level 2
3) Level 3

I 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 measure

CASE 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])
END


This 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 CUSTOMED
LEVEL 1 - LEVEL 2 - LEVEL 3

E EE EE04 100 90
E EE EE05 200 100
E EE EE06 300 200
E EE EE07 400 300
==== ====
TOTAL --E-- 1000 690 (Sub Total is correct !!!)

M MM MM01 10 9
M 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 7, 2013 8:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 563, Visits: 1,088
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 7, 2013 4:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 12, 2014 2:30 PM
Points: 83, Visits: 177
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse