September 23, 2004 at 7:53 pm
I am trying to create a calculated member that will reference a separate measure value in the ancestor just above and I am having difficulty figuring out how to write this or to use the wizard to help write it.
Here is my scenario.
I have a calculated member called [Avg Order Amt], this calculated member works just fine.
I want to create another calcuated member that is called [Order Amount Normalcy]. This new calculated member would be a percentage that is a measure of normalcy of the average to the overall average at the direct ancestor level. In other words, say for a particlar Customer you have an Order Average. The Order Amount Normalcy would be the Order Average / Average Order Amount for ALL Customers. I want this to be a measure so that it can be used with any dimension selections. For instance, Order Destination Zip Code...the Normalcy would be the Order average for 1 zip code / the order average across ALL Zip Codes. If the dimension we were considering was Product, the measure would represent the Average Oder Amount for that Product / the Average Order Amount for ALL Products. My thought here is that we can then filter on Order Amount Normalcy for anything over 115% and see things that are "Abnormal".
I am having a very difficult time figuring out how to create this measure in the calculated measures wizard.
Like I said, I already have the [Avg Order Amt] calculated measure working. What syntax do I need to use to create a calcuated measure that represents the [Order Amount Normalcy]?
Hopefully one of you knows exactly how to do this. Can anyone help?
Mindy
September 23, 2004 at 11:01 pm
Thinking more about it...I think what I need is the [Avg Order Amt] / [Avg Order Amt] for ALL
How do I write that in MDX?
Forget the Ancestor part, I don't think that is necessary.
September 24, 2004 at 3:31 am
Is this the kind of thing your looking for -
iif(([Measures].[Avg Order Amt],[Customer].CurrentMember.Parent) 0, ([Measures].[Avg Order Amt], [Customer].CurrentMember) / ([Measures].[Avg Order Amt], [Customer].CurrentMember.Parent), NULL)
September 24, 2004 at 8:51 am
Well, I want it to be a measure that works for any dimension that is pulled into the visible cube grid. So it would need to work with Customer, Product, ZipCode, etc...whatever the dimension is we are currently looking at. So hardcoding the [Customer] in there is going to create a problem, right?
I want it to be a measure that is the Average Order Amt / Average Order Amt for ALL - and it is dynamic for whatever dimension(s) you are browsing.
September 29, 2004 at 1:55 pm
Well, I finally figured it out myself. At least, I figured out a way to get what I need. It may not be the best way, but it works.
For each dimension I want to see this "ratio" for, I need to create a calculated measure (since I cannot figure out how to get it to default to the dimension being browsed).
For instance, if the dimension being browsed is Store…the ratio of how a store's avg order amount measures up to the avg order amount across all stores could be determined with the following MDX:
([Measures].[Avg Order Amt])/(([Measures].[Avg Order Amt]),Store.Parent)
Sheesh.
Someone please let me know if there is some way to parameterize that Store.Parent part to be CurrentDimension.Parent or something of that nature.
Mindy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply