An interesting task was brought up during our client project last week. We had created a control chart highlighting outliers based on a selected standard deviation. However, the client thought that since the audience for the dashboard would not be statisticians, it would be simpler to have the choice be a percentage of the total points in the view.

While I’ve built standard deviation control charts before, I had never tried one based on percentage before. Allow me to walk you through the process I followed. (Shout out to Gwilym for the assistance in wrapping my head around this one!)

The first step is to consider how far away each point is from the average, regardless of whether it is above or below. This can be done using the ABS function which returns the result as a positive value. The difference from average is calculated as follows using the table calculation function WINDOW_AVG:

Now that we have the difference, we need to rank each point by the percent away from the average that it is. This can be done with the RANK_PERCENTILE function, another table calculation.

When set to calculate at the cell level, this table calculation function assigns each point a percentage value away from the average, with the furthest point being 100%, and points at the average being 0%.

In this case, we need to compare the point’s percentile rank to the percent we want to highlight, so we’ll also need to include a parameter.

The Choose Percent of Data to Highlight parameter is just a range of decimals (formatted to be percentage) from 0 to 1, with steps at 0.1 (10%) intervals.

The opposite of the percent parameter (1-[Parameter]) has been used in the calculation so that the outer points are the ones highlighted.

So with this calculated field on colour, we can choose the percent of points away from the average to highlight!