2 Replies Latest reply on Nov 15, 2018 12:44 AM by david.waldmann.0

# Getting the average of all not NULL values per row

I've got the following table:

date

column1column2column3
07.11.2018110(null)90
08.11.201890(null)(null)
09.11.2018607090
10.11.201880(null)(null)

What I want to get is a additional column that gives me the average of all values that are not null. That means for the given example:

date

column1column2column3result
07.11.2018110(null)90100
08.11.201890(null)(null)90
09.11.201860709073
10.11.201880(null)(null)80

How do I have to write my calculated field "result"? Info: the values in column1, column2 and column3 are already AVG values.

I thought of something like this:

IF not ISNULL (AVG(column1)) THEN counter = counter + 1

IF not ISNULL (AVG(column2)) THEN counter = counter + 1

IF not ISNULL (AVG(column3)) THEN counter = counter + 1

IF counter !=0 THEN (value1+value2+value3)/counter else 0 END

How can I transfer this idea into a calculated field?

• ###### 1. Re: Getting the average of all not NULL values per row

Hi David,

This field will work to calculate your average:

sum( ifnull([Measure 1],0) + ifnull([Measure 2],0) + ifnull([Measure 3],0))

/

sum(

if isnull([Measure 1]) then 0 else 1 END +

if isnull([Measure 2]) then 0 else 1 END +

if isnull([Measure 3]) then 0 else 1 END)

Here's how it looks in Tableau

The Tableau file is attached.  Let me know if you have any questions.

Best,

Paul

1 of 1 people found this helpful
• ###### 2. Re: Getting the average of all not NULL values per row

Hi Paul,

works perfectly! Thank you very much!