Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gkakun
Helper III
Helper III

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 formulas:

Standard deviation of Consumption total for Consumption =
CALCULATE(
STDEV.P('Monthy Consumption'[Consumption]),
ALLSELECTED('Monthy Consumption'[Consumption])
)

 

Average I just use the average consumption.

 

Now I want to calculate outliers which will be average*2 STD. How can I calculate it when the data is looking like that? 

 

for example- for a specific part, the graph is looking like in the picture number 3, with STD of 38.87 and average of 99.92. I want the outlier to be the actual value only if the consumption of the specific month is higher than average+ 2*STD else it should be 0. 1.JPG2.JPG3.JPG

1 ACCEPTED SOLUTION

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
AlB
Super User
Super User

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.

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

 

 


@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)

Hi,

 

Any chance you looked at the row data? 

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

Hi,

 

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

Pls enter google drive of the below account:

glgl46540@gmail.com

password- 1!password

 

let me know if you have any issues 

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.