SSAS 2008 R2 - Problems with total of a calculated measure in Excel

  • 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 (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...

  • 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

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply