How to calculate average value with null values

[Anonymous]

My query is related to handling null values in calculating the average at row level. Suppose we have 4 columns.
COL1 COL2 COL3 COL4
11 12 11 NULL
24 22 NULL 33
44 NULL NULL 44
55 NULL NULL 55
My query is suppose we need to get average of all the 4 columns i.e. (COL1+COL2+COL3+COL4)/4. As some of column contains null value so we cannot use above method for finding the average i.e. col1+col2.... Also we cannot replace NULL with 0 using ISNULL() as it reduces the average value, in short we need to take only those columns in count which has value and need to hide null values i.e. in first row avg should be of (col1+col2+col3)/3. 
Please let me know if anyone has faced this scenario in past and what solution he/she opted?

[TomMartens · Accepted solution]

Hey,

I used this DAX statement to create a "calculated column"

the average = 
var theTable = 
UNION(
    ROW("value",'Table1'[Column1])
    ,ROW("value",'Table1'[Column2])
    ,ROW("value",'Table1'[Column3])
    ,ROW("value",'Table1'[Column4])
)
var theSum = 'Table1'[Column1] + 'Table1'[Column2] + 'Table1'[Column3] + 'Table1'[Column4] 
var theDivisor = 
COUNTROWS(
    FILTER(
        theTable
        ,[value] <> BLANK()
    )
)
return DIVIDE(theSum, theDivisor, BLANK())

All the columns have a numeric data tape like decimal.

I create a table from the columns that have to be considered using UNION(ROW(...),...)

I create a simple sum from the values of in the columns that have to be considered

I count the non empty rows in the table.

Based on my sample data this will retrun these results:

Hopefully this is what you are looking for,

Regards,

Tom

[Anonymous]

Hello Tom

How can I apply that solution when i have a unique colum, with some nulls values, and I need the average (as a metric) of that unique colum? Thanks

[Anonymous]

Hi @TomMartens,

Greetings !!

Excellent it is working well now. Thanks a lot.

[Anonymous]

Hi,

when i added "the average" calculated column into report, i validated data and feel it is coming incorrectly bcoz of getting disable of Don't summarize option.

Could you please help me on how to enable Don't summarize option under value field.

If you screenshot attached, will understand clearly. Thanks.