Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Analysis Services (2000)  Calculated Measure puzzle

Author  Topic  mcurnutt Starting Member 1 Post Posted - 2004-09-24 : 14:36:23 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]? 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? 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. Anybody know how to do this as a calculated measure? Mindy