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

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

    • gkakun's avatar
      gkakun
      Helper III
      7 years ago

      Hi,

       

      Thanks for very much for your help.

       

      Looks like something in the logic is not working. For example, IPN#208100645 for IS1 marked as an outlier. the consumption in the current month is 1040. the last 12 months average is 784.667. the STD is 448.61. the limit is 1681.9 and still, it marked as an outlier although the consumption in the current month is lower. 

      • gkakun's avatar
        gkakun
        Helper III
        7 years ago

        Hi,

         

        I found the problem. I removed the filter alexcept location and it works. Thanks a lot!