Calculated Member for SUM of distinct values by dimension

Question

I have a multidimensional cube in SSAS. My fact table looks like this with a reference to a dimension, a column for a value, and a column for the amount.

DimMember | TheValue | Amount

1 | 10 | 100

1 | 10 | 300

1 | 10 | 200

2 | 30 | 500

2 | 30 | 100

3 | 10 | 300

       |       50 |    1500

I want to create a calculated member in the cube that will sum up TheValue rows but only once per dimension. The Amount column should sum every row normally, but TheValue column needs to be the sum of one instance per dimension. So TheValue column should sum up to 50 in this example. 10 + 30 + 10, not the sum of every row.

I have seen examples of answers for DISTINCT values for the column, but that will not work in this case because the values do not have to be unique across the dimension members.

Any help is appreciated.

Answer 1

You should be able to pull this off directly with two helper physical measures, one a sum of TheValue, the other a count, and with a calculation of sum(existing dim, divide(SumValue,CountValue)). A better solution would be to create a derivative fact table as distinct DimMember, TheValue and use it as a source of another measure group, where the measure would be a straightforward sum.