There are lots of formatting options within Power BI that really elevate reports. Conditional formatting can assist in quickly calling attention to potential problems with your data and is available for table and matrix visualizations. To access conditional formatting select the paint roller icon from the visualization tab when you have your table or matrix selected.
Depending on the column type you can format background and font colors, add data bars or icons and access a web URL using a color scale, rules or field value.
Today we are going to look at using color scale and rules to apply formatting.
To apply select a column to format and move the slider to on, this will show the advanced controls link. Select this to configure formatting.
All formatting options require you to select the field and summarization type for the conditions. This field does not need to be the same one being formatted and does not have to be on the table or matrix.
Color Scale Example
When formatting by color scale here are the things to consider:
- Default formatting defines how blank cells will be treated.
- Minimum and maximum values can be set using values from the selected field or by custom values.
- The scale defaults to two colors. Select diverging to include a middle value color.
In our example we are formatting the background color of the sales territory column based on total sales (DOCAMNT). For blank cells default formatting has been set to treat as if their value is zero. Lowest and highest values are setting the scale and a diverging color was selected.
Here is what our table looks like.
Rule Example
Rules format based on cell values that can be greater than, equal to, or less than a value or if the cell is blank. Multiple rules can be combined, and the value can be a percent of the column values or a static number.
Rules cannot be based on the value of another field.
In our example we want to flag any project where the cost exceeded what was earned. A rule cannot be used to format if the contract earned is less than the actual cost directly, an additional step is needed.
First, we create a custom column subtracting the cost from the contract earned and any project where there was a cost overrun will be a negative value.
We are going to format the actual cost column based on the sum of our custom column. If the value is less than 0 the font will be red. Both the greater than and less than statements are required, so we set it as greater than 0 percent and less than or equal to the number 0. Here is how our table looks:
Multiple Rule Example
In this example we want to see which projects are performing poorly based on percent cost. Our custom column is going to divide cost by contract earned and give us a decimal number.
Using icon formatting we have set four categories. Numbers are used when we want to compare to the results in our custom column, and percent is used to fill out statements where we only have one value to use.
- 40%-80% cost shows a green circle.
- Over 80%, but less than 100% cost shows a yellow triangle.
- Over 100% cost shows a red diamond.
- Less than is set to 100 percent.
- Less than 40% cost is flagged with a grey arrow.
- Greater than is set to 0 percent.
Here is what our table looks like.
In our next post we will review adding data bars and URLs and formatting using field value.
-Jerica Coleman, CRM and Power BI Consultant
If you’d like to explore Power BI for your business, contact us.
