Calcuating average for a numerical + text mixed column

[Anonymous]

Hi,

I have a sales column in which some rows have numbers but some have random strings as well.

I want to eliminate the strings and only calcualte average for the numbers.

I have written this calculation but it doesnt work

calculate  sales = CALCULATE(AVERAGE('Table'[Sales]),filter('Table',ISNUMBER('Table'[Sales])=TRUE))

Sales column:

[Tahreem24]

@Anonymous ,
If you follow the said steps so you'll get your expected output:

[PhilipTreacy · Accepted solution]

HI @Anonymous 

Download this sample PBIX file with data and measure

You can create this measure

Sales_Avg = AVERAGEX(FILTER('Sales', IFERROR(VALUE('Sales'[Sales]) >= 0,0)), VALUE('Sales'[Sales]))

Regards

Phil

[Anonymous]

didn't work for me.

I have a column with the same problem (mixed number and text), plus I also want to filter it by a certain id, either with or without the id doesn't work!

Satisfaccion global = AVERAGEX(FILTER('Survey User Input Line', 'Survey User Input Line'[question_id.id]=571 && IFERROR(VALUE('Survey User Input Line'[suggested_answer_id.name]) >= 0,0)), VALUE('Survey User Input Line'[suggested_answer_id.name]))​

OR

Satisfaccion global = AVERAGEX(FILTER('Survey User Input Line', IFERROR(VALUE('Survey User Input Line'[suggested_answer_id.name]) >= 0,0)), VALUE('Survey User Input Line'[suggested_answer_id.name]))​

the error:

MdxScript(Model) (13, 96) Calculation error in measure 'Survey User Input Line'[Satisfaccion global]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

[Anonymous]

Hi Phil,

This works but im confused what your iferror and value dax is doing.

Can you explain

[PhilipTreacy]

Hi @Anonymous 

The IFERROR( VALUE() ) is a parameter for FILTER.

What is happening is that for each value in the 'Sales'[Sales] column, try to convert it to a number using VALUE().

If that causes an error then return a 0, otherwise return the actual number.

If an error is generated the filter looks like FILTER('Sales', 0) which returns nothing, and as an error will only be generated when you try to convert a non-numeric text value to a number - this filters out any letters/words.

The final result of FILTER is a column (it's actually a table, but anyway) that consists of the values

1

2

3

4

5

6

7

8

AVERAGEX goes through each row in that column adding up the values resulting from converting the text numbers to actual numbers using VALUE('Sales'[Sales]) and then working out the average.  

Regards

Phil

[Tahreem24]

@Anonymous ,

DO the below steps:

Step 1: Create a column like below DAX:

Sepearte Num =

VAR IsNum = IF(TableName[Mixed Column] in {"0","1","2","3","4","5","6","7","8","9"},1,0)

VAR num = IF(IsNum=1,(TableName[Mixed Column]))

RETURN num

Step 2: Make the above column to Whole number from format option

Step 3: Create a measure like below:

AVERAGE(TableName[Sepearte Num])