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
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"
Add a comment