Power BI Table Conditional Formatting [With Examples]

    Do you know how to apply conditional formatting in the Power BI table visual? Using the Cell elements format option, you can effortlessly apply conditional formatting within the Power BI table visual.

    Recently, I worked on a Power BI report where I found with the help of the Power BI table conditional formatting. Based on value, we can change the background color, font color, data bars, icons, and web URLs.

    In this Power BI tutorial, we learned what the Power BI table conditional formatting is and how to apply conditional formatting in the Power BI table.

    Power BI Table Conditional Formatting

    In Power BI, table conditional formatting allows us to change the appearance of your data based on certain conditions. With the help of conditional formatting in Power BI Table Visual, we can highlight data in text or numeric fields by applying color, icons, data bars, and web URLs.

    Check the screenshot below that shows the conditional formatting of the Power BI table visual:

    Power BI Table Conditional Formatting

    How to Apply Conditional Formatting in a Power BI Table

    Here, I will explain how to apply conditional formatting in the Power BI table visual.

    Here, I have a SharePoint list (Finance Sample) that contains below columns with various data types:

    ColumnsData Types
    ProductSingle line of text
    Month NameSingle line of text
    CountrySingle line of text
    Sale PriceCurrency
    Units SoldNumber
    SalesCurrency
    ProfitCurrency
    Gross SalesCurrency
    Apply conditional table formatting in Power BI

    Follow the below steps to apply conditional formatting in the Power BI table visual:

    1. Open Power BI Desktop and load the SharePoint list. Then, we can see the data set in the Data Panel.

    How to use Conditional Formatting in Power BI table visual

    2. Under the Home tab, expand Visual gallery(black box) -> Click the Table visual.

    Power BI table visual Conditional Formatting

    3. Then, use the +Add data option to add the Month Name, Units SoldSales, Profit, and Gross Sales into the Columns field.

    Apply conditional table visual formatting in Power BI

    4. Now, our Power BI table visual has been created.

    Conditional formatting of Power BI table visuals

    Now, Using the above Power BI table, let’s apply conditional formatting. with conditional formatting:

    • Power BI table visual conditional formatting using the background color
    • Power BI table visual conditional formatting using font color
    • Power BI table visual conditional formatting using data bar
    • Power BI table visual conditional formatting using icons
    • Power BI table visual conditional formatting using web URL

    Power BI Table Visual Conditional Formatting using the Background Color

    I will tell you how to format cells with color based on a value in the Power BI table visual.

    Now, I hope you have created a table visual. Follow the below steps:

    1. Select the Matrix visual -> go to the Format pane -> click Visual -> expand the Cell elements.

    format cells with color based on a value in the Power BI table visual

    2. Next, expand the Series and select the column to which you want to apply conditional formatting (in this case, Month Name). Depending on the value, conditional formatting offers options when selecting a text value column: it shows Background color, Font color, Icons, and web URL. Check the screenshot below.

    Conditional Formatting in Power BI Tables visual

    As the month name is textual data, it shows four options. here, my requirement is to change the background color.

    3. Next, enable the Background color. Then, you see the one Background color window open.

    Conditional Formatting Of A Table In Power BI

    4. In this window, select Format style as Gradient. it means that the lowest value is set to one color and the highest to another. Then select What field should we base this on? as Sales. After that, click OK.

    Power BI Conditional Formatting Tables Inside

    5. Next, conditional formatting applies to the Month Name column based on sales.

    Power BI Table Visual Conditional Formatting using the Background Color

    This way, you can apply Power BI table visual conditional formatting using background color.

    Power BI Table Visual Conditional Formatting using Font Color

    I will tell you how to apply conditional formatting using font color based on the Power BI table visual column value.

    1. Select the Matrix visual -> go to the Format pane -> click Visual -> expand the Cell elements -> select Sum of Unit sold -> enable the Font color.

    format the font color based on the Power BI Table visual

    2. After enabling Font color, you see the Units Sold column value font color automatically formatted based on value.

    Conditional formatting using font color in Power BI table visual

    3. To change font color, expand the Cell elements -> under the Font color, click fx. Then, the Font color window opens here. Select Format style as Rules, add some rules using +New rule then click OK.

    Power BI table conditional formatting Column

    4. The column value font color changes according to the value.

    Power BI Table Visual Conditional Formatting using Font Color

    You can change the font color in the Power BI table visual using the above steps.

    Power BI Table Visual Conditional Formatting using Data Bars

    Next, in the Power BI table visual, I will tell you how to format cells with bars based on their column values.

    1. Select the Matrix visual -> go to the Format pane -> click Visual -> expand the Cell elements -> select Sum of Seles -> enable the Data bars.

    Power BI format cells with bars based on their Column values

    2. After enabling Data bars, you see in the Sales column that data bars are added automatically based on value.

    Power BI Table Visual Conditional Formatting using Data Bars

    3. To change the Data bar color, expand the Cell elements -> Under the Data bar, click fx. Then, a Data bars window opens. We can set colors for the positive and negative values based on the maximum and minimum values in our data range. Then click OK.

    Changing Data Bar Colors based on Conditions in Power Bi

    4. The cell’s Data bar color changes according to the Column values.

    Conditional Formatting By Data Bar In Power BI table visual

    5. We can only show the Data bar and remove the font value. To do this, expand the Cell elements -> Under the Data bar, click fx -> check the Show bar Only check box -> click OK.

    Power BI - Add Data Bars to Table

    6. Then, you can see that the Sales column only shows the data bar.

    Power bi conditional formatting only data bar in table visual

    This way, you can format conditional using the Data Bar in the Power BI table visual.

    Power BI Table Visual Conditional Formatting using Icons

     In the Power BI table visual, I will tell you how to add icons to columns based on their values. It is also known as KPIs(key performance indicators) in Power BI.

    1. Select the Matrix visual -> go to the Format pane -> click Visual -> expand the Cell elements -> select Sum of Profit -> enable the Icons.

    Power BI Table Visual Conditional Formatting using Icons

    2. After enabling Icons, the icons are automatically added based on the value in the Profit column.

    Table visual conditional formatting with Icons in power BI

    3. We can change the icons. To do this, go to the Cell elements section -> below the Icons, click the fx. The icons window box opens. In this box, expand the Style box. We can set up 25 different pairs of icons. Choose one pair icon and click the OK button.

    conditional formatting in Power BI table visual

    4. The icons are added to the Profit column based on value.

    Conditional Formatting using Icons in Power BI table visual

    This way, you can add the icons to the table visual column in Power BI.

    Power BI Table Visual Conditional Formatting using Web URL

    I will tell you how to use web URL conditional formatting in the Power BI table visual.

    To achieve this, I created an additional table in Power BI with columns for Country, Sales, and Profit.

    Conditional Formatting with Web URL in Power BI table Visual

    1. Under the Home tab, click on the New measure.

    Power BI table visual Conditional Formatting based on Measure

    2. In the formula bar, put the below DAX expression. Then click Commit.

    Web URL = 
            SWITCH(
                    MIN('Financial Sample'[Country]),
                    "Canada","https://en.wikipedia.org/wiki/Canada",
                    "France","https://en.wikipedia.org/wiki/France",
                    "Germany","https://en.wikipedia.org/wiki/Germany",
                    "Mexico","https://en.wikipedia.org/wiki/Mexico",
                    "United States of America",
                     "https://en.wikipedia.org/wiki/United_States",
                    ""
    )
    Conditional Formatting based on Measure in Power BI table visual

    2. Select the Matrix visual -> go to the Format pane -> click Visual -> expand the Cell elements -> select Country -> enable the Web URL.

    conditional formatting using measures in power bi

    3. After enabling Web URL. A window Web URL comes. Here, choose the Web URL (Measure) in the What field should we base this on? Section. Then click OK.

    Applying custom conditional formatting using a Measure in Power BI table visual

    4. After that, a web URL will be displayed when you hover over a value in the Country column. Check the screenshot below.

    Power BI Table Visual Conditional Formatting using Web URL

    Using the above method, you add a URL to the table visual using the web URL conditional formatting.

    Conclusion

    I hope you know now how to apply conditional formatting in the Power BI table visual.

    In this tutorial, we covered the concept of conditional formatting in Power BI table visuals. We explored applying it using various options such as background color, font color, data bars, icons, and web URLs.

    You may also like:

    >