218.486.5095

Implementation Specialists

Microsoft Partner providing ERP solutions, support and consulting.

Home / Power BI / Power BI: Conditional Formatting Continued

//

In our last Power BI blog post we talked about applying conditional formatting to a matrix or table within reports. Today we are going to look at a few more types of conditional formatting that can be used.

Data Bars Example

Adding data bars helps to quickly identify high and low performance.

In this example we are adding data bars to the profit column.

  • Show Bar Only
    • If selected the values would not appear as numbers within the table or matrix, just the bar would appear. We have not selected this.
  • Minimum and Maximum
    • Defaults to lowest and highest values, but custom values can also be set. Custom values are most commonly used when data bars are used to measure against goals. We kept the range as lowest and highest values.
  • Colors
    • Colors for positive, negative and the axis can be customized.
  • Bar Direction
    • Can be left to right or right to left. As we want negative values to the left we have kept bar direction as left to right.

Here is how our table looks now:

Web URL Example

To add a web URL link there must be a column within one of the tables that is a URL. We have added the column weather to our table and these URL’s are links to weather.com for the city where the project is located.

Here is what the data table shows:

To avoid showing the long URL within our visualization table we are going to apply conditional formatting to the project column of our visual and select web URL.

To avoid showing the long URL within our visualization table we are going to apply conditional formatting to the project column of our visual and select web URL.

  • Format by
    • Defaults to field value as the URL within the column will be used.
  • Based on field
    • Weather column was selected as it holds the URL.
  • Summarization
    • Set to use the first value within that column for each row.

Here is how our visual table looks now:

Each project has a line underneath signifying that it is a hyperlink and hovering over the project shows the full link that will be navigated to if that project is selected.

Field Value Example

In the Web URL example we used the URL field value to apply formatting. This can be done with other field values when they are a type of formatting. In the next example we are going to use color.

In the data table a conditional column was created that color coded the project by profitability. Anything $0 or less is red, greater than $3000 is green and greater than $0 is yellow.

Here is how the data table looks:

Now let’s use this new column to apply formatting.

We have selected to format the background color of the profit column.

  • Format by
    • This is switched to field value.
  • Based on field
    • The color code column created is used to format the color.
  • Summarization
    • The first value is what will be used. As we do not have any duplicate project numbers there is only one value for each project.

Here is how our table looks now:

The same formatting could be applied to the font color and then only color would be seen. Field values can also be hex codes, RGBA values or HSLA values versus color names.

Now that we’ve covered the basics in conditional formatting, I recommend getting into Power BI and trying out conditional formatting for yourself. The best way to become comfortable doing something is to work with it using your data.

-Jerica Coleman, CRM and Power BI Consultant

If you’d like to explore Power BI for your business, contact us.