By default, I set the aggregation for LoanAmount to be sum. This is giving me a result of 700. This is not what I want, I want to be able to sum on the Distinct ID. The correct result should be 600. I set the LoanAmount to the aggregate function Max and
in a new calculated field I tried entering:
create currentcube.measures.SumAmount as
Sum([Dimension Deal].[Deal ID], [Measures].[Loan Amount])
However I keep receiving syntax errors? Is there any way else to do this without modifying the internal table data?
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]