Show boxplot outliers with dynamic STDEV

[Anonymous]

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!

[MAQSupport]

Hello @Anonymous,

You can refer to the Calculations document of Box and Whisker by MAQ software available at our GitHub page.

Thanks.

[Anonymous]

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]

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]

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