Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Show boxplot outliers with dynamic STDEV

Dear Power BI users,

 

I have Sales dataset consisting of (1) ProductCategory, (2) Customer, (3)NetSales, (4) Margin. Margin percentage was calculated with a calculated measure:

Margin% =DIVIDE(SUM(dataset[Margin]);SUM(dataset[NetSales]))

Based on this I made a Boxplot (Box and Whisker by MAQ Software) in order to detect outliers on Margin%.

 

Until so far this all worked out perfectly well.

 

However, next to my boxplot I also would like to put a table presenting the outlying customers per product category. As you can see I below I presented a list of all customers with their margin, but I would like to present only the outliers (note that the upper and lower whisker is on one standard deviation from the mean).

 

I tried to calculate the =STDEV.P on Margin% by myself, but this wasn't possible since Margin% is a calculated measure.

 

To summarise, I would like to know how I can calculate the standard deviation on a calculated measure (which is dynamic when I use filters on year-month, productcategory, etc.).  Or does anyone know an easier way to show outliers in this case?

 

Many thanks in advance for your help!

Need Help
Show and Tell

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I already tried to calculated stdev, mean margin and subsequently tried to determine the outliers.

     

    stdev = DTDEVX.P(dataset;[Margin])

mean margin = AVERAGEX(dataset;[Margin%])

outlier = IF([Margin%]<([mean margin] - [stdev]);1;0)

    See below: unfortunetely the three outliers have a value of 0 instead of the desired 1.

     

    The total stdev and mean margin on C are correct!

     

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      Hi Anonymous,

       

      Based on your image and the formulas, it seems that outliers is correct.

       

      I'm a little confused about your formula about stdev = DTDEVX.P(dataset;[Margin]).

       

      As I know, we should use STDEV.P(<ColumnName>)  to returns the standard deviation in Power BI. Are you sure you calculate the stdev is correct with DTDEVX.P function?

       

      If it is convenient, could you share some data sample and your desired output so that we could help further on it?

       

      Best Regards,

      Cherry

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi v-piga-msft

         

        Desired output is a matrix showing only outliers (from my boxplot).

         

         

        Sample data:

         

        ProductCategory Customer  Sales   Margin
        C Customer 1  € 234,00   € 3,00
        C Customer 2  € 23,00   € 1,07
        C Customer 3  € 34,00   € 2,28
        A Customer 1  € 47,00   € 4,49
        A Customer 2  € 543,00   € 55,39
        A Customer 4  € 234,00   € 25,18
        A Customer 5  € 23,00   € 2,47
        A Customer 6  € 34,00   € 3,66 
        A Customer 7  € 47,00   € 5,11 
        A Customer 2  € 543,00   € 59,73 
        B Customer 8  € 98,00   € 10,82 
        A Customer 2  € 633,00   € 70,39 
        A Customer 9  € 38,00   € 4,27  
        B Customer 8  € 84,00   € 9,44