Customize Combo Chart for detecting Outlier (showing shaded area)

August 14, 2021

Customize Combo Chart for detecting Outlier (showing shaded area)

Hello Everyone,

Recently I came across one requirement where we have to display the outliers based on standard deviation (SD) i.e Mean +/- 3SD or Mean +/- 2SD. Which will help analysts to understand how the sales data performing and can find best and worst day.

Outliers are a simple concept—they are values that are notably different from other data points, and they can cause problems in statistical procedures.

Understanding of Outlier finding from Standard Deviation (SD):

Below is a sample design which we had to implement in MSTR Dossier.

So, Today will see how we can implement the above requirement in MSTR-Dossier.

Below are the steps for creating the chart like above (Note: In this example I have created multiple derived metrics but you can create normal standalone metrics:

Firstly in this demo data is on Date and Week_Day level. We can have other levels also but that is as per the requirement. To keep it simple we have used only Date and Week_Day.
Now you have to create Mean Metric as:
Avg(Sales){![Week Day]+}
Then create Standard Deviation P Metric as :
StdevP(Sales){![Week Day]}
Now create 3SD Upper, 2SD Upper, 3SD Lower & 2 SD Lower as below:

3SD Upper: [Mean(Sales)]+(3*[Std deviation P])
2SD Upper: [Mean(Sales)]+(2*[Std deviation P])
3SD Lower: [Mean(Sales)]-(3*[Std deviation P])
2SD Lower: [Mean(Sales)]-(2*[Std deviation P])
Create a Outlier Dot Metric (which will be used to display a dot where ever there is a outlier) as:
Case(((Sales<[2SD - Lower]) And (Sales>[3SD - Lower]));Sales;((Sales<[3SD - Upper]) And (Sales>[2SD - Upper]));Sales;"")
For the upper shaded region we can use 3SD upper and 2SD upper but for lower shaded region it's not easy to do. So, to create the lower shaded region we have to create multiple metrics for different conditions. So, follow below formulas to create all the different metrics (Note: In the Github link you can find a excel file with all the detail formulas and Dossier to check):
Once you created all the metrics, now it's time to include the objects to chart. Include a Area Visualization and include the objects in the order shown in below image. (Note Order of metrics are crucial to create the shaded region correctly):
Once included all the objects, you have to make only Sales Metric Shape as Line. and Outlier Dot Metric as Circle.
Now, we have to change the colors of the metrics. Follow the color guide included in the excel file shared on GitHub link shared. for quick access below is the color guide:

That's it we are ready with the required chart 😃