Creating buckets by comparing Dimension member against Measure using MDX RRS feed

  • Question

  • I have a Product dimension which has hierarchies - product_id, initial_price, product_name, category and Measures - units_sold, cost_per_unit, revenue_per_unit. For each product_id I need to compare initial_price against revenue_per_unit and then assign it to a bucket based on the comparison. 

    Consider the following example:


    product_id   initial_price revenue_per_unit

    1               10              12

    2               20              18

    3               30              35

    And if Product 1, 2 are in Category Book and 3 in Clothes then output should look like


    Category  revenue type        Amount

    Book               Profit                  2

                          Loss                    2

    Clothes           Profit                   5

                          Loss                     0

    How can I achieve this using MDX?

    Thursday, January 9, 2014 3:41 PM