Most visuals in Power BI allow you to set the colour of values in a table, graph or any other visual dynamically using conditional formatting.
For example, in the screenshot below I have coloured the percentages (which represent growth compared to the previous year) based on 4 rules:
This works really well, and is also how most people usually apply conditional formatting to a visual.
Recently however, I was working on a project for a customer who could not yet tell me what the final colours would be. They were still analysing how many colour groups there could be, and what the limits of these groups would be.
The report to be created consisted of a large number of pages with all kinds of visuals, and the majority of these visuals needed to contain the same conditional formatting. Because it would be too much work to change the rules of the conditional formatting for every visual, we solved the problem differently.
Our solution: We created a measure that determined which colour should be used. Then we set up the conditional formatting so that the colour is determined by the measure. If we subsequently need more (or different) colours, we only needed to modify our measure.
If we were to apply this to the example above, we would create a new measure ‘Revenu (%YOY-Color)’ which looks like this:
The logic is as follows:
- We store the percentage in a variable called __Percentage
- Then we make some tests, and use the colour of that test which gives the result TRUE. Colours are represented by their hexadecimal code.
To find this code, you can use the standard pop-up to choose a colour. If you want to know the hexadecimal code of a colour on the screen, there are all kinds of tools such as: ColorPic, Instant Eyedropper, Eyedropper tool in PowerPoint…
Finally, we still need to set up the conditional formatting of the visual so that it uses our measure. You do this by choosing ‘Field value’ from ‘Format Style’. From the ‘What field should we base this on?’ drop-down list, you can select the newly created measure. You can only select measures that show text as a result. This makes it impossible to select most of the other measures.
You can easily update the measure later by:
- adding extra conditions
- using different colours
The result is that all visuals in the report that use this measure will be updated automatically.
More tips to make your work in Power BI easier? Our Power BI experts will be happy to provide them! Ask for an obligation-free quote for your Power BI support or Power BI training via saleslearning@xylos.com.
