Hi Alban,

In your scenario, there are two records for DealID 1, and you want to sum it only once right? What would be expected sum if DealID is not 100 in both cases? What if you have 200 and 300 LoanAmount for DealID 1? Do you want an average in
that case, first, last, minimal, maximal value or ...? If you want an average, then you can make 2 regular measures in your cube, one to do sum, the other to do count. Divide them and you'll have your average.

In MDX you would sum AVG LoanAmount over distinct accounts like this:

Sum( [Fact Table].[DealID ].[DealID ].MEMBERS,

[Measures].[LoanAmount ] / [Measures].[Count of fact items]

)

Regards,

Charlie Liao

TechNet Community Support