Forum Discussion

gkakun's avatar
gkakun
Helper III
7 years ago
Solved

Outliers calculation help needed

Hi all,   Need some help here.  I have this table, as shown in picture 1. and I used this table to show the graph as shown in picture 2.  I have calculated average and STD using the below formula...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi gkakun

     

    You may create measures as below. Attached the sample file for your reference.

    Current =
CALCULATE (
    SUM ( Sheet2[Consumption] ),
    FILTER ( Sheet2, Sheet2[Month] = "current" )
)

    Average = CALCULATE( AVERAGE(Sheet2[Consumption]),FILTER(ALLEXCEPT(Sheet2,Sheet2[Location]),Sheet2[Month]<>"current"))
    SDT = CALCULATE(STDEV.P(Sheet2[Consumption]),FILTER(ALLEXCEPT(Sheet2,Sheet2[Location]),Sheet2[Month]<>"current"))
    Mark = IF([Current]> [Average]+2*[SDT],"outlier")

    Regards,

    Cherie

AlB's avatar
AlB
Community Champion
7 years ago

gkakun

You can share the URL to the file from a platform like Dropbox, Onedrive, etc. or upload it to sites like this  (no account required)

gkakun's avatar
gkakun
Helper III
7 years ago

Hi,

 

Any chance you looked at the row data? 

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi gkakun

     

    It's better that if you could share the pbix file and expected output so that we could help further on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

     

    Regards,

    Cherie

    • gkakun's avatar
      gkakun
      Helper III
      7 years ago

      Sure. Link below.

       

      I added examples for a few parts. IPN is part number which can be set in few locations. each location has consumption for this part. the current month is the current month, month 01 is last month, month 02 is 2 months ago and so on. 

       

      now, I want to calculate outliers for the consumption. which means is the current month have consumption which is higher than 2 times the standard deviation in the last 12 month, it should be marked as an outlier. 

       

      for example, if part 123, in IS, consumed in the past 12 months- 1,1,2,3,2,3,4,3,3,5,6,4 so the average is 3.08 and the standard deviation is 1.44. which means is the current month consumption will be higher than 5.96 (3.08+1.44*2) will be marked as an outlier. 

       

      hope its clear

       

      Thanks in advanced.  

       

      https://drive.google.com/open?id=13dCYHvYcbjgnxECEpRU4UXEXwApScvRU

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi gkakun

         

        You may create measures as below. Attached the sample file for your reference.

        Current =
CALCULATE (
    SUM ( Sheet2[Consumption] ),
    FILTER ( Sheet2, Sheet2[Month] = "current" )
)

        Average = CALCULATE( AVERAGE(Sheet2[Consumption]),FILTER(ALLEXCEPT(Sheet2,Sheet2[Location]),Sheet2[Month]<>"current"))
        SDT = CALCULATE(STDEV.P(Sheet2[Consumption]),FILTER(ALLEXCEPT(Sheet2,Sheet2[Location]),Sheet2[Month]<>"current"))
        Mark = IF([Current]> [Average]+2*[SDT],"outlier")

        Regards,

        Cherie