Here is my dim_prod structure: prod->cat->subcat->type->sku
I have two measures: sales, forecast. I need to calculate "needed". The
problem is that the formulas needs to be different at different level:

At the sku and type level, needed = (Sales - |Sales - forecast|)/Sales.
At the subcat, cat and prod level, needed = (sales - (sum of
sales fc |sales-fc| needed

sku1 100 70 30 .7
(right)
sku2 100 110 10 .9
(right)
type1 200 180 20 .9
(right)
sku3 ...

sku4 ...

type2 200 220 20 .9
(right)
subcat1 400 400 0 1.0
(wrong)

the "needed" should be (400 - (20+20))/400 = .9. How do I do that? Is that
some type of custom rollup?

Define the "needed" calculated measure as follows:

'([Measures].[sales] - iif(
[dim_prod].CurrentMember.Level.Ordinal >=
[dim_prod].[type].Ordinal,
Abs([Measures].[sales] - [Measures].[fc]),
Sum(Descendants([dim_prod].CurrentMember,
[dim_prod].[type]),
Abs([Measures].[sales] - [Measures].[fc]))))
/ [Measures].[sales]'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!