Forum, Tips & Tricks

Learn tips and tricks and engage in discussions with our blog authors here. It’s all for your benefit and learning. 

You need to log in to create posts and topics.

Conditional formatting based on dynamic values of different cells

Conditional formatting based on dynamic values of different cell

Example: conditional formatting actual vs target

You want to compare actual vs target sales. The task is to highlight sales which are falling below the target.

At the current state, power BI is not able to do conditional formatting based on values of another cells. However, you can format the cells based on a calculated column.

Steps:

1. Create a calculated column

a) Go to Edit Queries

cond

b) Select the relevant dataset (table, excel sheet)

xx

 

c)  Add custom column

x

d)  Insert formula: Actual – Target. Create a suitable name for new column. Save and apply the changes.

x

2. Conditional formatting based on calculated column

a) Select the final value that you want to format

x

b) Left click a mouse and select conditional formatting -> background color or font color. In this case, we will use background color.

x

c) In “Format by” select “Rules” and in “Based on field” select the calculated column. In our case it’s “Difference”.

y

d) Fill in formatting conditions. In this particular case, the purpose is to highlight sales which are below target therefore a threshold of 0 is chosen.

x

e) Cells with actual sales below target will appear red as you can see in the following picture. The last step is to hide the “Difference” column by going to “Value” and unselect “Difference”.

f) Final outcome.

y

Thank you, very helpful. Learned a lot.