Conditional formatting on variance versions

Guido.kaandorp

Hi, I would like to apply conditional formatting on a dashboard. I have a module based on 3 versions.

-Actual

-Annual Plan (AP)

-Variance

 

Variance = Actual - AP

 

Now the conditional formatting should be green if the Variance >0 and red when <0. However when I apply conditional formatting all versions will be formatted.

I have tried the following to add an extra line item for the sake of the conditional formatting only, which results in a 0 value for the actual and AP version and with (potentially) a negative or positive number for the Variance version. However this would not help as the Variance version is overridden with the version formula (Actual- AP).

 

Any tips or trics will be appriciated.

nathan_rudman

Hello,

If you are using the variance version, adding an additional line item as you did might be the only way. Link your second line item to the first one, you should get the same values on the three versions. Then you can apply the conditional formating on the second line item only and hide the columns you don't want showing.

 

Guido.kaandorp

Hi Nathan, Thanks for your reply.

In my case this will not help unfortinatly. See screenshot as example. The lineitem is called week and below that I show the 3 versions. Only the B/(W) version (the variance one) should have conditional formatting.

If I add more lineitems and hide some columns, the table will look different.

sukethavarapu

Hi,

 

 

Create a new line item for the conditional format and input the formula so that for result would be -1 (for Green )  +1 (for Red).

Then, in the Blue Print under the "Formula Scope" select the applicable version where the conditional format to be applied, in this case it is Variance.

While applying the conditional format, choose 3-Color Scale and color combination Green, NoColor, Red.