For many companies, January is a month of reporting how well the previous year did or didn’t. In this post, I will walk you through a very simple method of adding conditional formatting to a column chart. Adding colours to indicate performance is a very quick method of giving a clear message.
Adding Conditional Formatting
For this post, I am using a basic table of fruit and total sales, which when plotted on a column chart gives a simple chart. We are going to change the chart so that the columns greater than the target are one colour and those greater than the target are another colour.
These instructions assume you have already loaded the data into the report.
Step 1: We are using a very simple target of one value for all fruit. We start by saving that value in a measure called Sales Target.
From the Modelling or Home ribbon click New Measure and enter:
Sales Target = 100
Step 2: The next step is to add 2 columns to the fruit sales table one for Below Target and one for Above Target. It will split the numbers into 2 columns.
From the Modelling Ribbon click New Column and enter the following:
Below Target = IF(FruitSales[Sales]<FruitSales[Sales Target],FruitSales[Sales],0)
Add another column with the following:
Above Target = IF(FruitSales[Sales]>=FruitSales[Sales Target],FruitSales[Sales],0)
The difference between the two columns is the operator, first, one is < less than the target and Above is >= greater than or equal to the target.
Step 3: Return to the report canvas and add a stacked column chart.
- Add Fruit to the Axis
- Add Above Target and Below Target to the Value
- I also sorted by Fruit Ascending to get the order.
Without any more ado, the columns are in 2 colours based on the measure.
Conclusion
This very simple trick I used to teach in Excel and was surprised it was needed in Power BI, but it works well. I hope your January reporting goes well. I will do a more complex variation of this later in the year that includes individual targets that can be time-based.
