Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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?
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
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
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
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |