Conditional Formatting in Power BI Tables

by Hardik Established Member on ‎05-29-2018 09:34 PM - last edited on ‎05-31-2018 05:42 PM by MindyJ Frequent Visitor

While working on Power BI, I came across a feature I found really impressive and easy to implement. It not only enabled me to present my information in a structured and concise format but also made the reports I designed look informative.

 

Conditional formatting is a feature in Power BI and Excel that allows us to specify customized cell colors based on cell values, other values or fields by using gradient colors. You can also display cell values with data bars in order to easily detect patterns and insights from the numbers.

 

To access conditional formatting, in the Fields well of the Visualizations pane in Power BI Desktop, select the down-arrow beside the value in the Values well that you want to format (or right-click the field). You can only manage conditional formatting for fields in the Values area of the Fields well.

 

1.png

 

To remove conditional formatting from a visualization, just right-click the field again, select Remove conditional formatting and then the type of formatting to remove.

 

2.png

 

Power BI Desktop May Update-Conditional Formatting by A Different Field

3.png

 

Before Update

4.jpg

After Update

 

Now, whenever you open the conditional formatting dialog, you’ll see two new dropdowns. The first, Color based on, provides you with the option to pick what field from your model to base your rules on, and the second, Summarization, is where you’ll pick the aggregation type for that field. There is also an Apply color to box that lets you know what field in your table or matrix is being formatted currently.

 

The Color Based on and Summarization drop downs auto-populate the same filed name you wish to apply conditional formatting on. In order to customize or change the fields for formatting, a drop down containing the table names and field names will appear and you can choose the required field according to your requirement.

To use discrete color values for given value ranges, select Color by rules. To use a color spectrum, leave Color by rules unchecked.

 

When you select Color by rules, you can enter one or more value ranges, each with a set color. Each value range starts with an If value condition, an and value condition, and a color.

5.png

You can also choose the Minimum and Maximum values along with their colors. If you select the Diverging box, you can choose an optional value as the center for the distribution of color patterns.

 

 6.png

Below is an example for the same:

I have a table where the reasons for denials are categorized and the amount denied rolling 12 months and prior rolling 12 months is displayed.

7.png

I have used font color scales for the prior rolling 12 field and background color scales for the rolling 12 denied.

8.png

I chose sum of prior rolling 12 as the criteria to display various colors on the data. The minimum value is shown in red and the highest one is in green. Similar procedure has been followed for the second column for background color scales.

 

This is a brief account of a formatting feature along with the latest updates. I hope all the readers find this blog informative.

Thank You 

 

Comments
by ashishvadhan New Member
on ‎06-05-2018 04:55 AM

This solution helped me too, I was seeking related solution.

Thanks

by gauravgarg726 New Member
on ‎06-05-2018 04:56 AM

Nice Solution! That heplsSmiley Happy