Forum Discussion
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.
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
