Hi,
AVG() function yields wrong results so i changed the formula from
avg() to sum() dividing by count of childrens to get average

I am trying to calculate Stock availabilty index % based on last 7
days run rate and net stock available for a day

below is the formula

Stock Availabilty Index % =
IIF([Measures].[RS SAI test]=0,0,IIF(Not
IsLeaf([Product].[Category].Currentmember),(SUM([Product].[Category].currentmember.children,[Measures].[RS
SAI test])/[Measures].[Count of Items]),[Measures].[RS SAI test]))


[Measures].[RS SAI test]) is calculated as below

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

[Measures].[Count of Items] is calculated as

[Measures].[Count of Items]=
Sum([product].[category].currentmember.children,IIF([Measures].[RS
1DayRR Tn] = 0,0,1))

this count of item ignores whose Run rate value is 0 and puts as 0 and
1 as applicable and when selected top level gives correct count of
children by summing all 1's

The stock availabilty Index% calculates correctly to some point of
time
till you find childrens count whose value is greater than 1 and fails
when children count is 1.

for eg:
Suppose levels are brand,Brand variant,Plan,SKU5
Calculates correctly [Measures].[RS SAI test])at SKU5 level based on
the number of children,rolls correctly till Brand variant calculating
averages based on the children count.
the point of failure is at Brand level it has got only one Brand
Variant there it doesn't take the value of it's children and divide by
1 instead applies the [Measures].[RS SAI test]) formula at this level
takes the min as run rate /runrate * 100 and gives 100%

How can i handle such situation to roll out correctly till the top
level of the hierarchy taking the children values and dividing by
count of children at each level.

How can i tweak the formula.

Please suggest

Since you are doing sum/count, which is a simple rather than weighted
average, just use Avg() of Filter():


'Avg(Filter([Product].[Category].CurrentMember.Children,
[Measures].[RS 1DayRR Tn] > 0),
IIF([Measures].[RS 1DayRR Tn] <
[Measures].[RS Stock TN], 100,
([Measures].[RS Stock TN] * 100)
/[Measures].[RS 1DayRR Tn]))'
- Deepak

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

Hi,
thanks a lot with your idea i could arrive at this formula
for calculating averages

IIF([Measures].[Rs 1dayRR
Tn]=0,0,Avg(Filter(Descendants(Product.category.Curre ntMember),[Measures].[RS
1DayRR Tn]>0),IIF([Measures].[RS 1DayRR Tn] < [Measures].[RS Stock
TN], [Measures].[RS 1DayRR Tn],[Measures].[RS Stock
TN])*100/[Measures].[Rs 1dayRR Tn]))

which is working fine.

I had created pivot table based on this cube now
but descendant function takes too much of time since product has got
around 22 thousand members.
Every time i change the members of other dimension takes too much of
time around 2 minutes to show the values

is it advisable to continue with the above formula
the currently I am working on the server itself and created the Excel
pivot table on the server itself.Still takes this much of time.

If it takes this much time on the server itself how much will it take
if a person queries it from WAN connecting to Analysis server.

Secondly i didn't understand why did you hard code 100 in your
suggested formula
Please reply


Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote