MDX Express to count only Non- NULL records

• Bobby Rao

  SSC Journeyman

  Points: 81

  More actions

  June 8, 2005 at 1:50 pm

  #110892

  Hello:

   I am trying to compute the Averages of the Sales_amounts, Sales_Margins, Sales_Markups.

  These measures are present in the Fact Table.  I've dimensions are as usual--based on geography, Time, Tool_Type, Tool_Purchase_Type.

  The only caveat is that there could be some nulls for records of Sales_Amounts, sales_Margins or even Sales_Markups and also sometimes Zero as the value. I shouldn't consider the records with NULL values but should consider the cells with Zero for counting and for averages. If I consider the cells with Nulls, that would distort my averages.

    I tried to use count(Geography_ID) in memebr properties and populated a field in Measures table, but I believe that count takes into account

    even the cells with Null value. So, this doesn't help me at all.

     I computed Count = count(Geography_Id) separately and then computed average using

     [Measures].[Sale Amount]/[Measures].[Count].

    Can I anyway use Avg(Descendants([Time].[2005],[Quarter]),Measures.Sales_Amount).

     It gives me a syntax error. I guess I'm going wrong some where.

   

    I read about ExcludeEmpty option in Columns, but faced with syntax problems. I guess I had somewhere wrong.

     Any help is highly appreciated.

    

     Thanks,

     Bob

• June 13, 2005 at 8:00 am

  This was removed by the editor as SPAM

• Tejal Sumeet Deshpande

  SSC-Addicted

  Points: 418

  More actions

  June 14, 2005 at 1:48 am

  #565186

  Hi,

  there is syntax mistake in

  Avg(Descendants([Time].[2005],[Quarter]),Measures.Sales_Amount).

  it should be [Measures].[Sales_Amount]

   

  if you want to avoid nulls and zeros in calculating Avg then use filter.

   

  You can use following two methods to calculate Avg

   

  1.

   

  sum(filter(Descendants([time].currentmember,1),(([measures].[Measures].[Sales_Amount])>0)))

  /count(filter(Descendants([time].currentmember,1),(([Measures].[Sales_Amount])>0)))

   

  2.

  Avg(filter(Descendants([time].currentmember,1),(([measures].[Measures].[Sales_Amount])>0)))

   

  Note: Make changes according to your requirment.This is genralized expression for all year's quarter.

   

  hth