-
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
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|