Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calcuating average for a numerical + text mixed column

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:

 

 

Need Help

6 Replies

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    5 years ago

    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])
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Phil,

       

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

      Can you explain

      • PhilipTreacy's avatar
        PhilipTreacy
        Icon for Super User rankSuper User
        5 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

       

       

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    5 years ago

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