Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter matrix by standard deviation

Hi   Is there a way I can filter my results in a matrix using standard deviation? For example my matrix has numerical values in the columns going down, and I would like to make blank the values whi...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Is this what you want?

    Measure = 
VAR sd_AvgMileage =
    CALCULATE ( STDEV.P ( 'Table'[Avg Mileage] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        MAX ( 'Table'[Avg Mileage] ) > sd_AvgMileage + 2
            || MAX ( 'Table'[Avg Mileage] ) < sd_AvgMileage - 2,
        BLANK (),
        MAX ( 'Table'[Avg Mileage] )
    )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

The information you have provided is not making the problem clear to me. Can you please explain with an example. Can you share sample data and sample output.

Appreciate your Kudos.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sure here is an example. I have two columns, there are also rows which correspond to each row which are car registeration plates. Both of these columns go into the 10,000s in terms of length.  I want to calculate the standard deviation for each column, and then have the data values which exceed 2 times the standard deviation to be converted to BLANK for each column.

 

 
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    • Icey's avatar
      Icey
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

       

      Is this what you want?

      Measure = 
VAR sd_AvgMileage =
    CALCULATE ( STDEV.P ( 'Table'[Avg Mileage] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        MAX ( 'Table'[Avg Mileage] ) > sd_AvgMileage + 2
            || MAX ( 'Table'[Avg Mileage] ) < sd_AvgMileage - 2,
        BLANK (),
        MAX ( 'Table'[Avg Mileage] )
    )

       

       

      Best Regards,

      Icey

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.