Introduction

This article will show how to set conditional formatting on a specific value based on another one which doesn’t directly relate to it. For example, when one wants to show a total of revenue in a specific region and set its color to green in case the market share in the same region has reached the goal of 15%.

Steps

To format the first value based on the second value performance, we will create a function that returns a number greater than 1 in case we didn’t achieve the goal and smaller than 1 else.

Step 1 - create the indicator

In this case, the first value is the revenue of USA. In the above example, we used the pre-saved Revenue formula and the country as filter, using measured values.

The secondary value in this case will be the contribution of the USA number of orders. starring this formula is recommended for future usage.  

Step 2 - Set the conditional formatting

For this example, the goal for USA market share is 15%. The function we will use will be our goal divided by the secondary value. If we reach the goal, this function will return a fraction, otherwise it will be greater than 1 because the denominator will be smaller than the numerator.

We will multiply the primary value by this number and use it for our conditions.

the result:

When we don’t reach the goal → the function will return a greater than 1 number → the product will be greater than the first value → the first condition will apply and the indicator will present red text

When we reach the goal → the function will return a fraction → the product will be smaller than the first value → the second condition will apply and the indicator will present red text

The mathematical explanation for the formula will be:

USA REVENUE  Vs.  (USA REVENUE)*(GOAL/USA MARKET SHARE)

After dividing the formula by ‘USA REVENUE’ this formula is equivalent to:

1  Vs. GOAL/USA MARKET SHARE

Apply it and you are set. 

Good luck!