cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SantoshKumar Regular Visitor
Regular Visitor

Calculate Average and Standard Deiation as per selection criteria

Hi,

I need to create a table with outlier values with below condition.OutlierTb1.JPGTable1.JPGwithoutOutlierTb1.JPG

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
Community Support Team

Re: Calculate Average and Standard Deiation as per selection criteria

Hi @SantoshKumar ,

 

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.
SantoshKumar Regular Visitor
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.

SantoshKumar Regular Visitor
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?
i did following steps but it showed no results.
for Outlier table,
added measure to visual filter and changed filter condtion as equal to 1.