Cap Percentages

When calculating percentages to use in a graph, there are sometimes edge cases that generate extreme percentages. This throws out the graph and prevents the user from focusing on the material values.

If you have determined that the extreme percentages are not valid representations of the data, you can cap the percentages generated for use in the graph. This is done by capping the source data using a formula.

The general formula uses a combination of the MAX and MIN functions.
To limit the percentage to a range of 0% to 100%, a generic formula would look like this:

=MAX(0,MIN(A1,1))

To limit the percentage to a range of -50% to 150%, a generic formula would look like this:

=MAX(-0.5,MIN(A1,1.5))

If percentages are converted to a number, zero percent will be displayed as zero. One hundred percent is displayed as one.

How the formula works.
If the A1 cell contains a value greater than 100%, is is constrained to minimum value of 100% by the MIN function otherwise the actual value is returned.
And, if the A1 cell contains a value less than 0%, is is constrained to the value of 0% by the MAX function.

It is always best to avoid hard coding magic numbers into formulas.
The absolute values of zero and one in the formula, can be replaced by cell references.

=MAX(B$3,MIN($A7,B$4))

Always be careful when capping your data, as it can hide real issues.
If you capping between 0% and 100%, I would instead cap between -1% and 101%.
This gives you a small hint when data is capped. If you see -1% or 101% in your graphs data labels, investigate!

Capping can also radically change the profile of the data.

In this graph, there is no capping is applied.

In this graph using the same data, capping from 0% to 100% is applied.

DAX

This capping approach can also be applied in DAX formulas. Here capping of -1% to 101% is applied to a margin calculation.

Margin:=MAX(-0.01,MIN(DIVIDE(([Revenue]-[Cost]),[Revenue],0),1.01))