-1

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!

user10454387
  • 1
  • 2

1 Answers1

0

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.

Woody
  • 1,677
  • 7
  • 26
  • 32
  • thanks Woody!! at least it gives me ideas what to use. thanks again! – user10454387 Oct 08 '18 at 01:57
  • tried your answer and it actually worked! But how do I aggregate them? where do I put AGGREGATE? thanks again. – user10454387 Oct 08 '18 at 02:29
  • How about if there are 2 variables? For example, if dog and red, multiply by 5. If dog and yellow, multiply by 6. if cat and red, multiply by 8, if cat and yellow, mulitiply by 9. We have 'Animals' and 'Color' dimension.How should the CASE WHEN syntax look like? – user10454387 Oct 18 '18 at 07:57
  • WITH member measures.[ColorQuantity] AS CASE WHEN [Product].[Product].currentmember.member_key = "red" and [Animals].[Animals].current_member.member_key = "dog" THEN measures.[Quantity] * 5 WHEN [Product].[Product].currentmember.member_key = "yellow" and [Animals].[Animals].current_member.member_key = "cat" THEN measures.[Quantity] * 9 /* Add other conditions here */ ELSE measures.[Quantity] END SELECT { measures.[Quantity], measures.[ColorQuantity] } ON 0, Non EMPTY {[Product].[Product].[All].Children * [Animals].[Animals].[All].Children } ON 1 FROM YourCubeName – Woody Oct 25 '18 at 01:44
  • In the case statement you'll notice I've used currentmember.member_key. There are other values you might need to use in place of member_key. These are most likely going to be member_name or member_value, depending on how your cube dimensions have been set up. A full list of intrinsic member properties can be found here: https://learn.microsoft.com/en-us/sql/analysis-services/multidimensional-models/mdx/mdx-member-properties-intrinsic-member-properties?view=sql-server-2017 – Woody Oct 25 '18 at 01:45
  • Thank you @Woody! That was very helpful. But how do I aggregate them? There is no amount in the Grand Total. thanks again. – user10454387 Oct 31 '18 at 01:54