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

Hi gkakun

 

Could you share a sample of your pbix file or otherwise a table/text copy of the data you show in the first image? Just to make things easier for people trying to help.

gkakun's avatar
gkakun
Helper III
7 years ago

Sure. Sorry for the ignorance. but how do I upload excel file to here? 

 

 

  • 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

      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

      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