Hi,
I have cube which has following levels in the Product dimension

-ALL products
--PP
--Category
---Segment
----Brand
-----BrandVariant
------Plan
-------SKU5


the formula calculates currectly at SKU5 level and yields assume 67 %
at one SKU5 but other SKU5 assume there are 3 other SKU5's are under
plan which have 100 % calculated but when rolling upto Plan yields
100% and not 91.75%

Below is the formula,morover there are 5 regular dimension and one
Brand as virtual dimension in the virtual cube

the formula in which i am trying to use CoalesceEmpty function for
averaging still yields incorrect result

this begins the formula inside the line

================================================== ==============================
IIF(IsLeaf([Product].[Category].CurrentMember)

or IsLeaf([Time].[JC].CurrentMember)

or IsLeaf([CP Non CP Type].CurrentMember)

or IsLeaf([Branch].CurrentMember)

or IsLeaf([Customer].[Sales].CurrentMember)


or IsLeaf([Depot].CurrentMember),((CoalesceEmpty(IIF([Measures].[RS
1DayRR Tn]= 0,0,IIF([Measures].[RS 1DayRR Tn] < [Measures].[RS Stock
TN], [Measures].[RS 1DayRR Tn] / [Measures].[RS 1DayRR Tn]
,[Measures].[RS Stock TN]/[Measures].[RS 1DayRR Tn]))))) * 100,

IIF(Not IsLeaf([Product].[Category].Currentmember),Avg([Product].[Category].CurrentMember.Children,CoalesceEmpty(Measures.Cur rentMember,0)),

IIF(Not IsLeaf([Time].[JC].Currentmember),Avg([Time].[JC].CurrentMember.Children,CoalesceEmpty(Measures.Cur rentMember,0)),

IIF(Not IsLeaf([CP Non CP Type].Currentmember),Avg([CP Non CP
Type].Currentmember.Children,CoalesceEmpty(Measures.Cur rentMember,0)),


IIF(Not IsLeaf([Branch].Currentmember),Avg([Branch].CurrentMember.Children,CoalesceEmpty(Measures.Cur rentMember,0)),

IIF(Not IsLeaf([Customer].[Sales].Currentmember),Avg([Customer].[Sales].CurrentMember.Children,CoalesceEmpty(Measures.Cur rentMember,0)),


IIF(Not IsLeaf([Depot].CurrentMember),Avg([Depot].Currentmember.Children,CoalesceEmpty(Measures.Cur rentMember,0)),Measures.CurrentMember)))))))

================================================== ==============================
Let me explain this formula
If I select product hierarchy or any other hierarchy on the rows
depending on the leaf level it should calculate the formula

((CoalesceEmpty(IIF([Measures].[RS 1DayRR Tn]= 0,0,IIF([Measures].[RS
1DayRR Tn] < [Measures].[RS Stock TN], [Measures].[RS 1DayRR Tn] /
[Measures].[RS 1DayRR Tn] ,[Measures].[RS Stock TN]/[Measures].[RS
1DayRR Tn]))))) * 100

else it is not leaf taking which ever dimension is on row take
AVG,assume
product dimension is on row

Avg([Product].[Category].CurrentMember.Children,CoalesceEmpty(Measures.Cur rentMember,0))

There are 6 question which i want to ask?
1)Is it the correct way i am doing
2)Still averages are not rolling properly
3)Will it work if i have 2 dimensions on the row
4)How can i tell not use the member in Average if it's value is
zero,tried using Coalsceempty function still doesn't help
5)using Measures.CurrentMember is the right way of doing
6) Any other easiest way.

Please help it's urgent.

Regards
Prasanna

An update to the query asked
The time dimension is at page level
Product dimension at row level
the report needs to calculate at the lowest level of time i.e day
which is a Page level field.
Since [RS 1DayRR Tn] measure is calculated at day level the time
dimension has to be at lowest level of date at page field.
But the formula calculates correctly at Leaf level of Product(SKU5)
and Leaf level of time(date) but the averages are not rolled up
properly

Please help.



tawargerip (AT) hotmail (DOT) com (Prasanna) wrote in message news:<d4b0bad7.0401140042.51bc71a3 (AT) posting (DOT) google.com>...