CODEWITHSUNDEEP
exceldaxpowerpivot
Tart
Tart

Reputation: 305

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

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?

Upvotes: 1

Views: 4596

Answers (2)

Strawberryshrub
Strawberryshrub

Reputation: 3399

Best way here would if you replace your NULL (string) values with the database null (nothing) value. This way your rows stay null but your calculation still works.

Go to Edit Queries > Transform > Repalce Values and replace your string NULL to a database null. Your table will look like this then:

enter image description here

Now the avarage calcualtion is simple:

enter image description here

Database null values get ignored from every calculations. This is way it works.

Upvotes: 1

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

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.

Upvotes: 2

Related Questions