Forum Discussion

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(SU...

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
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
  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