SSAS Distinct Sum Calculation


  • I have a fact table with repeated data like so:

            Deal ID      LoanAmount
            1                 100
            1                 100 
            2                 200
            3                 300

    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?

    Friday, January 10, 2014 12:02 AM


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


    Charlie Liao
    TechNet Community Support

    Friday, January 10, 2014 9:22 AM