Conditional formatting is a robust feature in Power BI that you can leverage to help users gain insights. Typically, we see conditional formatting applied to the table and matrix visuals. For example, you might apply a background color, font color, or icons to the values based on a set of rules.
Conditional formatting of tables is valuable, but the capabilities in Power BI go beyond that. You can even conditionally format shape colors!
This week, we are going to incorporate conditional formatting throughout our report. We will apply conditional color formatting to visual backgrounds, borders, and bars. As you can see in the example below, the colors dynamically change as you select different regions and bars on the charts.
- Get the data.
- Change the default visual interaction from cross highlighting to cross filtering.
- Options and settings > options > CURRENT FILE > Report Settings
- Optional – After importing the data, use or create a dedicated calendar table with the following columns: Date; Year; Month Name; Current Year (True/False); Prior Year (True/False); Month Number.
- Connect the calendar table to the orders table on the date field with a many to one relationship.
- Create measures for:
- Sum of Current Year Sales
- Sum of Prior Year Sales
- Year-Over-Year (YOY) Percent Change
- Create a measure that returns one color when YOY sales are positive and another color when YOY sales are negative. I used the following hex codes:
- #689CCA for the blue
- #F59C4E for the orange
- Create four visuals:
- Current Year Sales
- YOY Sales Indicator
- Current Year Sales by Category (I used the Bullet Chart by OKVIZ)
- Current vs. Prior Year Sales by Month (I used the Bullet Chart by OKVIZ)
- Configure conditional formatting on the YOY Sales Indicator, Sales by Category, and Sales by Month visuals.
- Add a filter for region.
- Add a custom legend using text boxes and shapes.
This challenge uses the SuperStore dataset provided on data.world. You will need to log in to data.world to retrieve the data, but accounts are free.
After you finish your workout, share on Twitter using the hashtags #WOW2022 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd, @KerryKolosko, and @NerdyWithData. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.