locked
Need MDX Expression for picking up the distinct values and sum it . RRS feed

  • Question

  • Hi All,

    I have a situation where particular measure have lot of repeating values against the date dimension. I need to create calculated member where it has the sum of the unique values 

    Eg.,

    1.       In the case of more than 1 distinct value , we need the sum of distinct values .

    e.g.,

    1. 10

    2. 10         10

    3. 10

    4. 10

    5. 20

    6. 20         20

    7. 30

    8. 30

    9. 30        30

    10. 30

    The sum of the distinct values is 10 + 20 + 30 = 60

    Can anyone please help on this issue. 

     

    Thank you

     


    Saraf
    Friday, July 15, 2011 4:28 PM

Answers

  • Use the DISTINCT function

    Something like
    CREATE MEMBER CURRENTCUBE.[Measures].[SumDistinct] AS
    SUM(<<Date Dimension>>.<<Hierarchy>>.CurrentMember, Distinct(EXISTING(<<Date Dimension>>.<<Date Level>>*<<Measure Name>>)).Item(0).Item(0));

     

    HTH


    -Remember to mark as helpful/the answer if you agree with the post.
    • Marked as answer by Challen Fu Monday, July 25, 2011 10:52 AM
    Friday, July 15, 2011 6:08 PM

All replies

  • Use the DISTINCT function

    Something like
    CREATE MEMBER CURRENTCUBE.[Measures].[SumDistinct] AS
    SUM(<<Date Dimension>>.<<Hierarchy>>.CurrentMember, Distinct(EXISTING(<<Date Dimension>>.<<Date Level>>*<<Measure Name>>)).Item(0).Item(0));

     

    HTH


    -Remember to mark as helpful/the answer if you agree with the post.
    • Marked as answer by Challen Fu Monday, July 25, 2011 10:52 AM
    Friday, July 15, 2011 6:08 PM
  • Thank you Naveen for the prompt response. Actually, I was looking for measure against two dimensions(Date and Channels) but I got that solved in excel itself(@for reports). If you can post the expression against two dimensions then it would help full for someone in the blog.

     

    Thank you once again!!!

     


    Saraf
    Tuesday, July 26, 2011 1:32 AM