Power BI Blog: Conditionally Formatting Negative Numbers
11 June 2020
Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau takes a look at how to apply conditional formatting to negative numbers.
When creating reports in Power BI, many people would like to see negative numbers emphasised in some manner. One of the most common requests is to have negative numbers highlighted in red.
Highlighting numbers in red is a fairly simple task in Excel. However, that is not what we are considering here. We will review how to apply conditional formatting to fields in Power BI so that negative numbers will be highlighted red automatically.
For this example, we will be using the simple data table, DummyRevenue:
After pulling this data table into Power BI, we may create the appropriate measures to calculate the Total Revenue, Total Cost and the Profit. For your reference, there measures are:
Total Revenue = SUM(DummyRevenue[Revenue])
Total Cost = SUM(DummyRevenue[Cost])
Profit = [Total Revenue] - [Total Cost]
We can then create a simple Matrix visualisation:
As you can see, we have negative profits (i.e. losses) for the months of September and October.
Let’s add conditional formatting to colour the negative profits for that month to be red. The first step is to select the visualisation, then navigate to the Format tab in the Visualizations panel. Scroll down, and in the ‘Conditional Formatting’ area, we will see a drop down box to choose the field we wish to conditionally format. In our case, we select the Profit field and enable the ‘Font color’ option.
The default conditional format is a colour scale. To change it, we click on the ‘Advanced controls’ option next to the ‘Font color’ option. This will call the ‘Conditional formatting’ dialog box.
By default, it will be set to ‘Color scale’. We want to change it to Rules.
The trick here is to set the rules of the first drop-down box to be ‘If value is greater than or equal to’ and type in ‘Minimum’ followed by the Number option in the third drop-down box. Then, we should select ‘is less than’ in the fourth drop-down box and then ‘0’ (zero) followed by Number.
The Matrix should look like this:
There you have it! A neat little trick you may adopt to conditionally format negative numbers in your Power BI reports.
That’s it for this week, come back next week for more on Power BI.
In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.
