Calculating Averages & Excluding Blanks

[parzival1220]

Hi there,

Please could I ask for some help creating a measure? I need to create a measure that calculates the average for a set of values in a column. The problem is that some of the values are blank, in Transform these show as 'Error', as it's a calculation of days between two dates and one of the date fields has blanks. The column I'm using (Days to Resolve.Days) was created in Power Query as seen below. No matter what DAX I use I awlays get a syntax error message or that the parameter is not the right type. All I need to do is find the average value excluding blanks. DAX won't allow me to use IF or ISBLANK as it throws an error stating it can't find the column (it only finds my other measures). I'm a bit stuck to be honest 😣. Any hlp would be greatly appreciated. Thanks. 

[BIswajit_Das · Accepted solution]

Hello @parzival1220  can you share a demo data table with demo values to get better understanding your need.
But here for now you can use

column =

calculate(
average(col1),
filter(tbl_name,col <> blank())
)

[parzival1220]

Thanks @BIswajit_Das & @Ahmedx . That seems to have done the trick! 😀

[Ahmedx]

instead of 0 you can write null

[Ahmedx · Accepted solution]

pls try this

[BIswajit_Das · Accepted solution]

Hello @parzival1220  can you share a demo data table with demo values to get better understanding your need.
But here for now you can use

column =

calculate(
average(col1),
filter(tbl_name,col <> blank())
)