basic mdx question using Ms Excel OLAP tools

Question

I will make this question and scenario as basic as possible since I have no background on programming. How do I make a script where all red will be multiplied by 5, yellow by 6 and blue by 7? The new measure will aggregate in grand total. I don't know what expressions to use. Just use [Product] for the colors and [Measure] for qty.

enter image description here

I dont understand yet the use of MEMBERS and other expressions as this is my first time to be on it. I tried

([Measure].[Quantity],[Product].&[Yellow])*6

but it will just multiply everything with 6. Maybe FILTERS? IIF? I just don't know how. The script will go a long way when I will apply it in our database. thanks!

Woody Woody · Accepted Answer · 2018-10-05T01:52:55

I know you asked about doing this with excel, but if you were writing an MDX query you could do create a new measure and run the query like this:

WITH 

member measures.[ColorQuantity] AS CASE WHEN [Product].[Product].currentmember.member_key = "Yellow" THEN measures.[Quantity] * 6
                             WHEN [Product].[Product].currentmember.member_key = "Blue" THEN measures.[Quantity] * 5
                              WHEN [Product].[Product].currentmember.member_key = "Red" THEN measures.[Quantity] * 2
                             ELSE  measures.[Quantity] END

SELECT {
measures.[Quantity], measures.[ColorQuantity]
} ON 0,
Non EMPTY 
{
[Product].[Product].[All].Children  /// I dont know the FULL dimension AND hierarchy path you want TO use
} ON 1
FROM YourCubeName

This might help you get started.

basic mdx question using Ms Excel OLAP tools

1 Answers