cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate Average and Standard Deiation as per selection criteria

Hi,

I need to create a table with outlier values with below condition.

Outlier Table  = [TTR] > (AVERAGE(TTR) + 3 * (STD(TTR)))

where TTR = EndTime - StartTime (It will be in Positve always, currently ignore those which have negative)

Here Average and Standard deviations are showing up for entire data irrespective of selection(Year/Months filter).
i am looking for any conditons which has to be applied to get  average and STD values dynamically as per selection.
Reports shows the Table after appling exclude values of Outlier conditon.
I tried  MonthlySum  inplace of TTR but not able to get the excpected result.
currently not able to attach the file, will update the thread at the earliest.
3 REPLIES 3
Community Support Team

## Re: Calculate Average and Standard Deiation as per selection criteria

Calculated table is static once it has been initilized, it won't be dynamically changed based on slicer selection.

In your scenario, you need measures instead. Please refer to below measure, add it to "visual level filter", and set its value to 1.

```Measure =
VAR _average =
CALCULATE ( AVERAGE ( 'TTR Table'[TTR] ), ALLSELECTED ( 'TTR Table' ) )
VAR _Stdev =
CALCULATE ( STDEV.P ( 'TTR Table'[TTR] ), ALLSELECTED ( 'TTR Table' ) )
RETURN
IF ( SELECTEDVALUE ( 'TTR Table'[TTR] ) > ( _average + 3 * _Stdev ), 1, 0 )```

Best regards,

Yuliana Gu

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

## Re: Calculate Average and Standard Deiation as per selection criteria

@v-yulgu-msftThanks for the response. i will implement your measure and update you on the result.

Regular Visitor

## Re: Calculate Average and Standard Deiation as per selection criteria

@v-yulgu-msft

i have few question on your solution, probably i may be worng or i am getting it right. please correct me.

i need to apply the filter to Table:

OutlierTable = FILTER('TTR Table','TTR Table'[TTR] >(AVERAGE('TTR Table'[TTR]) + 3 * STDEV.P('TTR Table'[TTR])))
so i changed it as below:
OutlierTable = FILTER('TTR Table',('TTR Table'[TTR] >
(CALCULATE(AVERAGE('TTR Table'[TTR]),ALLSELECTED(('TTR Table'))))
+ 3 * CALCULATE(STDEV.P('TTR Table'[TTR]),ALLSELECTED('TTR Table'))))
both the conditons are yeidling same results to me.
you said to create a measure and apply visual filter but how can we apply to a table?