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 thi...
Need Help
  • 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.