Average formula

[Former Member]

Hi there,

I am trying to tweak a MDX formula to compute an average of base members excluding those with a specific property value.

To make it clear, assume I have an account A stored and need to calculate an account B which is the average of A of all base TIME members below the current TIME member excluding members that have the PERIOD property equal to "AUD".

Without the excluding, I get it to work with the following formula:

'IIF([TIME].Currentmember.Properties("CALC")="Y",AVG(Descendants([TIME].CurrentMember,,LEAVES),[ACCOUNT].[BANKBALCASH]),[ACCOUNT].[BANKBALCASH]),SOLVE_ORDER=2

But when I try to filter out like this, it won't work:

'IIF([TIME].Currentmember.Properties("CALC")="Y",AVG(Filter(Descendants([TIME].CurrentMember,,LEAVES),[TIME].[PERIOD]<>"AUD"),[ACCOUNT].[BANKBALCASH]),[ACCOUNT].[BANKBALCASH]),SOLVE_ORDER=2

Thanks,

Regis

[Former Member]

Hi Regis,

Are you trying

IIF(TIME.Currentmember.Properties("CALC")="Y",AVG(Filter(Descendants
(TIME.CurrentMember,,LEAVES),[TIME].[PERIOD]
<>"AUD"),ACCOUNT.BANKBALCASH),ACCOUNT.BANKBALCASH),SOLVE_ORDER=
2

There is a not equal to sign between

[TIME].[PERIOD] and "AUD"