#WOW2022 W43: Advanced Formatting by Jeremy Anderson

2 Comments / community, Power BI, Workout Wednesday / By

Introduction

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.

Requirements

  1. Get the data.
  2. Change the default visual interaction from cross highlighting to cross filtering.
    • Options and settings > options > CURRENT FILE > Report Settings
  3. 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.
  4. Create measures for:
    • Sum of Current Year Sales
    • Sum of Prior Year Sales
    • Year-Over-Year (YOY) Percent Change
  5. 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
  6. 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)
  7. Configure conditional formatting on the YOY Sales Indicator, Sales by Category, and Sales by Month visuals.
  8. Add a filter for region.
  9. Add a custom legend using text boxes and shapes.

Dataset

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.

Share

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. 

Solution

Download the completed .pbix file.

2 thoughts on “#WOW2022 W43: Advanced Formatting by Jeremy Anderson”

  1. Ellen

    Is there a way to add a diverging colour gradient to the bars that is conditional on the value of a measure, e.g. % variance from target? This is so easy in Tableau 🙁

  2. Shannon Lindsay

    Hi Ellen, you sure can! With the bar chart selected, go to ‘Bars’ in the formatting pane, where you’ll see an option for ‘Colors.’ Click on the little formula button and select format style, Gradient, and then select your measure from the dropdown labeled ‘What field should we base this on?’ I hope this helps!

Comments are closed.

Scroll to Top