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

gkakun's avatar
gkakun
Helper III
7 years ago

Pls enter google drive of the below account:

[email protected]

password- 1!password

 

let me know if you have any issues 

AlB's avatar
AlB
Community Champion
7 years ago

gkakun

May I suggest you just provide the URL to the file rather than making public your google-drive login details? I guess you don't have anything confidential there

  • gkakun's avatar
    gkakun
    Helper III
    7 years ago

    its OK, it's not my google account. just created it for this effort.