DAX calculate an average of a column when taking cells that contain only numbers

Question

Using PowerPivot functionality to create measures.
There are two types of values in a column: integers and NULL (string).
Trying to figure out how to create a DAX formula to calculate an average for the column that would take only cells that contain integers.
It feels that the next basic formula would work:

=CALCULATE ( 
        AVERAGE ( tData[columnA]),
        FILTER (    tData, [columnA] <> "NULL" )
               )

but it throws an error: The function AVERAGE takes an argument that evaluates to numbers or dates and cannot work with values of type String.

I'm wondering if there is a way to avoid this error without removing/cleaning NULLs values beforehand?

Answer 1

Power BI tables cannot contain mixed types of values in a single column. If there is numbers and texts in a column, the column type is text. Numeric values included in the column is not actually numbers, they are just texts composed of numeric characters.

So, in order to make it work, you need to explicitly convert numeric strings to real numbers.

= CALCULATE(
    AVERAGEX( tData[columnA], VALUE(tData[columnA]) ),
    tData[columnA] <> "NULL"
)

However, I suggest not to have text and numeric values in one column. Use Power Query to either remove rows with "NULL", or convert them to BLANK.