locked
MDX SUM of Averages RRS feed

  • Question

  • Hi guys

    Can you help me create a mdx query that will show the SUM of my Averages. I have a column showing [Number of Trailers] which is calculated by Num of Trailer  / count. However when calculating the grand total (excel) it shows the results of Sum(sum) / sum(count) hence the total will be an average. i.e.

    New York UK New Jersey Total (Wrong) New Total
    Trailers 6 8 10 8 24

    I want the Total to be 24 not the averaged 8

    Please assist.

    Tom

    Tuesday, July 11, 2017 10:54 AM

Answers

  • Hi Tom,

    Thanks for your response.

    In this scenario, you may need to create a dynamic set for selected cities. See below MDX expression:

    Create Dynamic Set CurrentCube.[selected cities]
    As
    {EXISTING [Geography].[City].[City]};
    
    SCOPE([Measures].[Number of Trailers]);
    SCOPE([Geography].[City].[ALL]);
    THIS=SUM([selected cities],[Measures].[Number of Trailers]);
    END SCOPE;   
    END SCOPE; 
    

    I am glad to know that my solution works for you. Please kindly mark them as answers.By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Tom2017 Thursday, July 13, 2017 5:49 PM
    Thursday, July 13, 2017 1:22 AM

All replies

  • Hi Tom,

    Thanks for your question.

    In this scenario, you can try to overwrite this Total with SCOPE statement as below:

    SCOPE([Measures].[Number of Trailers]);
    SCOPE([Geography].[City].[ALL]);
    THIS=SUM([Geography].[City].[ALL].CHILDREN,[Measures].[Number of Trailers]);
    END SCOPE;   
    END SCOPE; 

    You can write this SCOPE statement with script view in calculation Tab of your cube design window.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Wednesday, July 12, 2017 2:28 AM
  • Hi Willson

    Thanks for the feedback. I have put the code you stated and its working ok.

    However it is giving me grand total which will be the total for all cities. Can you  modify it for me so that it only shows the total for only selected cities. i.e. when i select New York and New Jersey the total should be 16 and should not pick 24.

    Thanks

    Wednesday, July 12, 2017 2:05 PM
  • Hi Tom,

    Thanks for your response.

    In this scenario, you may need to create a dynamic set for selected cities. See below MDX expression:

    Create Dynamic Set CurrentCube.[selected cities]
    As
    {EXISTING [Geography].[City].[City]};
    
    SCOPE([Measures].[Number of Trailers]);
    SCOPE([Geography].[City].[ALL]);
    THIS=SUM([selected cities],[Measures].[Number of Trailers]);
    END SCOPE;   
    END SCOPE; 
    

    I am glad to know that my solution works for you. Please kindly mark them as answers.By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Tom2017 Thursday, July 13, 2017 5:49 PM
    Thursday, July 13, 2017 1:22 AM
  • hi Willson

    Thanks a lot. your solution worked!!!

    Tom


    Software Developer

    Thursday, July 13, 2017 5:51 PM