×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Aggregation function max problem

## Aggregation function max problem

(OP)
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?

Best Regards,

Jose Ignacio.

### RE: Aggregation function max problem

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

### RE: Aggregation function max problem

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.

### RE: Aggregation function max problem

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.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!