# Need MDX Expression for picking up the distinct values and sum it . • ### 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

Thank you

Saraf
Friday, July 15, 2011 4:28 PM

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