6 Replies Latest reply on Mar 8, 2018 7:56 AM by paolo.raia

# Averages ignoring NULLS

Hi,

I would like to average a set of dimension figures and exclude NULL values, however I cannot seem to get the calculation correct (if there's a null value, the average is returning null):-

Example:

Dim1.....Dim2.....Dim3.....Dim4.....Average

3............3............2............NULL....Should calculate as 8 divided by 3 and not 4

3............3............2............4...........Should calculate as 12 divided by 4

etc

• ###### 1. Re: Averages ignoring NULLS

Hi, Paolo

If you can provide a sample workbook will be more helpful

In meanwhile, you probably can try sum(if isnull([dim]) then 0 else 1 end) as your denominator

ZZ

• ###### 2. Re: Averages ignoring NULLS

Hi Paola ,

If you need to calculate it on dimension create a calculated field "X"  having same value as of your dimension .Drag it to measures and use normal average formula .

sum and average of above mentioned data .

-Nikher

• ###### 3. Re: Averages ignoring NULLS

Try:

SUM([DIM]) / CountD([DIM])

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.

• ###### 4. Re: Averages ignoring NULLS

Thank you ZZ, Nikher and Joy for your feedback.

I have attached a workbook with the data.  So I need to find the average for each row.

Many thanks and regards,

• ###### 5. Re: Averages ignoring NULLS

Hi, Paolo

Please find my solution attached as well as screenshot below

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 6. Re: Averages ignoring NULLS

Many thanks ZZ!  That is the calc I was looking for.