Problem with MAX aggregation function

[ibdiez]

Hi
My name is Jose Ignacio and I'm working for a software firm in Bilbao (Baque Country, Spain).

Actually I'm developing olap cubes in analisys services for a large institutional customer.

I forward this email to you cause I'm getting desperated with one problem I've not been able to solve yet. I thought it would be easy to solve but I'm afraid it's isnt that simple.

I made a cube that has the following measures: Amount and Budget.
This measures are repeated for every rows of fact table.

Tha Budget makes reference to the Customer Code, "Division"
dimension,Posting Year and Posting Month.

I try to provide this data in this situation:


Measures.[Budget] (MAX aggregation mode)


Filter Posting Year 2004
Filter Posting Month genuary


Budget
Sell To Customer No Division Total
BERG ? -
DO ? 15.000,00
PM ? 40.000,00
BERG Total ? 40.000,00
General Total ? 40.000,00


When I check the single Budget the result is correct.
But in the total it returns MAX of rows. I need the SUM. (55000) (DO+PM)

What I'm trying to avoid with max aggregation function is to avoid sum amounts when there're repeated records.

Ex:

customer Amount Document

John 200 1

John 200 2

John 200 3


John should have the amount of 200. He has three documents with one single amount of 200. The sum aggregation would result in 600 (200+200+200) for John and that is incorrect.

Max function applied on amount measure gives me the correct results when checking amount for each customer but when I get the total result for 100 customers it always gives the max of all the rows instead of retrieving the sum of customer amounts.


What aggregation function other than MAX should I use to get correct total amounts for each customer (avoid sum of all the repeated amounts for the customer) and for the customer amount grand total?

Thanks in advance


Best Regards,

Jose Ignacio.

[Marcus Scherer]

Why not use SUM() aggregate function?

Marcus