# Thread: MDX help! Max then Sum on different sets

1. Registered User
Join Date
May 2009
Posts
2

## MDX help! Max then Sum on different sets

I have data like:

Dimension1: system_name
Dimension2: function_name
Measure: value1

system_name function_name value1
-------------------------------------------------
system1 function1 2
system1 function1 10
system1 function3 3
system2 function3 8
system2 function4 7.1

First, I'd like to group by system_name and function_name to get maximum of value1 on each group. Then I want to sum these maximum values group by only system_name.

Step 1: group by system_name and function_name to get maximum of value1 on each group

system_name function_name max_value1
-------------------------------------------------
system1 function1 10
system1 function3 3
system2 function3 8
system2 function4 7.1

Step 2: sum these maximum values group by only system_name.
system_name max_value1
-------------------------------------------------
system1 (10+3)
system2 (8+7.1)

I already add a calculated measure max_values into the cube for maximum on value1 so that I can do the first step. But how to do both steps in one MDX query? Please Help! I can do any dimension/fact table or cube changes if needed for this.

Any input / idea will be highly appreciated! Thanks!
Last edited by haskell; 05-21-2009 at 02:08 PM. Reason: add more information to make it clearer

2. DBJ Author
Join Date
Jun 2004
Location
Atlanta and Manhattan
Posts
607

## Hope I Understand the Requirement ...

Simply create a fresh calculated member (better, here, than derived upon the first calculated member) to do the second summing, and then call both within the column / row axis of the query.

Does that make sense?

Bill

3. DBJ Author
Join Date
Jun 2004
Location
Atlanta and Manhattan
Posts
607

## Were You Able to Reach a Solution?

Were you able to reach a solution? Please advise if so, or if we can assist further.

Thanks.

Bill

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•