dcsimg
Results 1 to 3 of 3

Thread: MDX help! Max then Sum on different sets

  1. #1
    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. #2
    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. #3
    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

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
  •