Forum Discussion

gkakun's avatar
gkakun
Icon for Helper III rankHelper 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

gkakun's avatar
gkakun
Icon for Helper III rankHelper 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
Icon for Helper III rankHelper III
7 years ago

Hi,

 

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