Forum Discussion

flavio29's avatar
flavio29
Frequent Visitor
4 years ago

Remove outliers based on standard deviation

Hello,  i have the following data:

 

Date

(database)

Code

(database)

Values

(database)

Avg values per codeAvg values per monthStdev based on avg values per monthAvg values per code correctedAvg values per month correctedStdev based on avg values per month corrected
1/1/202111115631,032,1  32,1  
1/1/202111115633,2      
2/1/202111115734,133,6  33,6  
2/1/202111115732,0      
2/1/202111115734,6      

3/1/2021

111189

35,03533,61,23533,61,2
4/2/202111143220      
4/2/20211114322122     
6/2/2021

111432

34      
6/2/2021

111432

3534,5  34,5  
8/2/2021

111464

32      
8/2/2021

111464

3131,529,35,331,5331,5

 

I already have the measure to calculate the average values per code and the stdev 

 

 

Average_values_per_code =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Table'[Code], 'Table'[Values] ),
        'Table'[Code] = SELECTEDVALUE ( 'Table'[Code] )
    ),
    'Table'[Values]
)
standard deviation based on average values per code = STDEVX.S(VALUES('Table'[Code]),[Average_values_per_code]))

 

 

 

But i need to ensure that stdev_Feb/21 <= stdev_Jan * 1,25. If not i need to remove outliers of the measure [Avg values per code] to ensure stdev will always be inside this limit for every consecutive month, so based on Avg values per month of Feb/21 I wanted to stablish limits to remove these values, such as    

 

 

 

Avg_value_of_Feb21 - stdev_Jan21 * 1,25  < Avg values per code corrected < Avg_value_of_Feb21 + stdev_Jan21 * 1,25

 

 

 

This will remove the outlier value of 22 and correct the stdev of Feb/21 to be inside the limit (1,2+1,25=1,5), and i need to do something similar to every month but i don't know if this is possible to do. 

 

Thanks for the attention.

Need Help

2 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi,  flavio29
    I was unable to reproduce your column using your formula above. Is the formula above a measure or a calculated column? Could you use the data to give an example of how you get the desired result
    Would you consider providing sample files?

     

    Where I am:

    It makes it easier to give you a solution.

    1. Sample (dummy dataset) data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • flavio29's avatar
      flavio29
      Frequent Visitor
      4 years ago

      Hello, v-angzheng-msft 

       

      The formulas are all measures. Below is a print of an example of my data:

       

       

      Just added the column "element of code", i dont think thats relevant becuase i dont need that for the formulas. Ignore the total becuase there is more data than that in the table.

       

      For the average per month or another concatenated data the values dont appear like i mentioned above, but if i concatenate the data in a table by month or year it gives the right result. Similar to standard deviation i used this for the average: 

       

      Avg values per month = AVERAGEX(VALUES('Table'[Code]),[Avg values per code])

       

       

      The major problem for me is what comes next because i can't remove the outliers based on the standard deviation like i showed previously. In the end i need a table for example like this (with corrected values which i don't have right now):

       

       

      Is it because the formulas are in measures instead of calculated columns?

       

      Thanks for the attention.