none
SSAS Calculated Member RRS feed

  • Question

  • Hi,

    I have the below case where I need to have sum of sales with respect to product as shown below.

    I have 3 dimension, selection any of which the Sum sales columns should give the total sales for each product. This formula should be applicable even if two dimensions are selected.

    Please let me know if anyone has Idea on it.

    Thanks in Advance.. :)

    Thursday, December 26, 2013 10:29 AM

Answers

  • This is fine. But what happens if we swap the dimensions. I want a general solution which should work even if we swap the dimension it should work.

    I got solution for this. 

    Thanks,

    Rajesh S Hegde

    Friday, December 27, 2013 8:16 AM

All replies

  • Hi Rajesh,

    In MDX, you can create a calculated member, which is a member based on other members. You can define two kinds of calculated members: ones that are measures and ones that are not. So in your scenario, we can achieve the requirement by add a calculated member. Here is a sample query.

    WITH MEMBER Measures.x AS SUM
       (
         {[Date].[Calendar].currentmember*[Date].[Month of Year].members}
         , [Measures].[Internet Sales Amount]
         )/2
    select
    {[Measures].[Internet Sales Amount],Measures.x} on 0,
    nonempty({[Date].[Calendar].[Calendar Year].members*[Date].[Month of Year].members}) on 1
    from [Adventure Works];

    Results:

    Reference:Overview of Calculated Measures and Members

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, December 27, 2013 7:15 AM
    Moderator
  • This is fine. But what happens if we swap the dimensions. I want a general solution which should work even if we swap the dimension it should work.

    I got solution for this. 

    Thanks,

    Rajesh S Hegde

    Friday, December 27, 2013 8:16 AM