# MDX help! Max then Sum on different sets

• 05-21-2009, 08:14 AM
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!
• 05-25-2009, 03:41 PM
Island1
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
• 06-16-2009, 03:09 PM
Island1
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