Forum Discussion

Navyaragamm's avatar
Navyaragamm
Frequent Visitor
2 years ago

Calculate average in a calculated column

Hi , 

 

I am stuck on some calculations, please help me out 

 

The issue is the result> (Average+2*STD.DEV) and result< (Average-2*STD.DEV) if both are true then the result is yes or no

Here the result is the value of the row 

And average is average of the value within one component and standard deviation is deviation of the value within one component 

Here to calculate the outlier what is best a calculated column or a measure 

Also can anyone help with the dax on how to compare the values 

 

 Above are the calculations and nab free are components 

I should be able to get by outlier based on the above calculation 

Also, I want to have a slicer of the same to filter 

Can anyone know how to do all the calculations 

Help is appreciated 

Need Help

2 Replies

  • foodd's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hello Navyaragamm ,  perhaps you mistakenly added your Excel post to the Fabric Community where the Community Members focus upon all things Microsoft Fabric related.     For your convienience we are supplying the link to the Excel (microsoft.com) Community.

     

     

    • Navyaragamm's avatar
      Navyaragamm
      Frequent Visitor
      2 years ago

      Hi foodd 

       

       

       

      Excel sheet was an example of the calculations,i want to replicate it in the power bi desktop

       

      I tried calculating it the power bi taking upper and lower dax measures and in a calculated column I have done the comparison but all I get is No

      Upper = 

       

      Var avg = average(data[value])

       

      Var std = stddev.p(data[value])

       

      Return avg+2* std

       

       Same for lower

       

      Then in the calculater column I have used 

       

      Outlier = if(data[value])> upper && data[Value]< lower , " Yes" , "No")

       

       

       

      If I take this the measure instead of column the visuals are loading And I also want a slicer of the outlier field