Aggregation function max problem

[ibdiz]

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.



How can I get the sum getting correct total amounts for each customer and for the grand total?




Thanks in advance



Best Regards,



Jose Ignacio.

 

[blom0344]

Add a additional measure that stores the Amount divided by the number of documents for each customer.

You will need to calculate this measure prior to building the cube (in the ETL process ?)

Customer Amount Doc Amount2

John 150 1 50

John 150 2 50

John 150 3 50

Summing Amount2 yields 50+50+50 = 150


Ties Blom

 

[MDXer]

You can crete a document count measure, how you do this depends on the underlying engine, and then create a calculation which is amount / document count.

 

[MDXer]

if you are using SSAS as your OLAP engine you can specify your aggregation type as FirstChild or LastChild not sure if this will fully fit in your situation. It all depends if the base measure can and how it is rolled up through various levels. You can probably do it with an MDX calculation that is pretty creative.