2 Replies Latest reply on Sep 13, 2016 2:41 PM by Elisa Begner

    Calculation to ignore text values in an average?

    Elisa Begner

      I want to create a calculated field that is an average, however, some of the values in my data set are text rather than numeric.  How does the calculation need to be written so that it will ignore all non numeric values? 

       

      In excel, I would use average if and have a condition for "<>idle", but I don't know how to do that in Tableau.  Please help!

       

       

      Here is an example of what my dataset looks like, I want to average all values except for idle ....

       

      Performance

      100

      0

      80

      96

      idle

      9

      100

      23

      idle

      17

      89

        • 1. Re: Calculation to ignore text values in an average?
          Tom W

          When you connect to this data, Tableau will interpret it as a string and it will be available in the Dimensions pane. If you don't need the 'idle' values at all, simply right click the field and select 'Change Data Type > Decimal'. Tableau will automatically return any non numeric values into a null so your average will work fine.

           

          If you want to keep the performance field as a string so you can still analyze the 'idle' instances, create a calculated field to create a 'numeric' version of this field. I.e. PerformanceNumeric = float([Performance])

          Similar to above, this will drop out non-numeric instances in the field and replace them with null so you can run an average on the new PerformanceNumeric metric by adding the performancenumeric measure to the report and changing the aggregation to AVG.

          • 2. Re: Calculation to ignore text values in an average?
            Elisa Begner

            Thank you Tom!  Changing the data type to decimal did change the numeric values to null.