Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How to calculate average value with null values

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?

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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:

image.png

 

Hopefully this is what you are looking for,

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

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:

image.png

 

Hopefully this is what you are looking for,

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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
Not applicable

Hi @TomMartens,

 

Greetings !!

 

Excellent it is working well now. Thanks a lot.

Anonymous
Not applicable

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.

 

don't summarize disbaled.PNG

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors