2021 Week 7 | Power BI: Conditional Formatting

4 Comments / Power BI, Workout Wednesday / By

Introduction

We’ve started to build a great repertoire of Power BI data modeling and visualization techniques over the past few weeks. For this week’s challenge, we focus on using three of Power BI’s five conditional formatting options to extend the design of a matrix visual.

In this case, you’ll create a set of conditional formatting rules to display different ranges of maximum ice coverage over time for the US Great Lakes. The unique combination of Background Color and Font Color creates something akin to Tableau’s “highlight table”. Once you have the basics of Power BI’s conditional formatting down in this exercise; you’ll have a foundation to extend your own reports with custom formatting using KPI icons, table heatmaps, data bars, and more.

Requirements

  • Find a Power Query transformation that will change the individual lake columns into rows (you’ll end up with only three columns loaded into your data model)
  • Create a matrix visual that displays ice coverage by lake (on columns) and year (on rows)
  • Include major format options as seen in the sample, such as removing row and column totals
  • Find the “lake” you need to filter out and add a page-level or report-level filter to exclude it
  • Matrix conditional formatting
    • Background Color
      • Build a set of conditional formatting Rules (not the default gradient Color Scale option)
      • Use five different colors for ice coverage consisting of 0-24.99, 25-49.99, 50-74.99, 75-99.99, and 100
    • Font Color
      • Only use black (#000000) or white (#FFFFFF) font colors
      • Build and apply Rules so that the contrast between your background colors and white or black font color passes the accessibility test for WCAG AA at https://contrastchecker.com
    • Icons
      • Add a star next to any value where the maximum ice coverage is 100%
  • Add at least two supporting visuals around your matrix. You only have three fields, so be creative!
  • Answer the following questions:
      • Which lake has frozen completely over most often?
      • Is ice coverage trending *upward* over time for any lake?

Dataset

This week uses maximum ice coverage data from NOAA’s Great Lakes Environmental Research Laboratory. A lake can have ice coverage ranging from 0 (no ice) to 100 (frozen over).

You can get the raw data from Data.World and use Power BI’s built-in Data.World connector, or alternatively get it in Excel format from GitHub.

If you’re only interested in visualizing data and not transforming it for this challenge, use this PBIX from GitHub (requires Power BI Desktop December 2020 or later).

Share

After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. 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

[embedyt] https://www.youtube.com/watch?v=hU12Y7Cd6n8[/embedyt]

4 thoughts on “2021 Week 7 | Power BI: Conditional Formatting”

  3. Robert

    To get the data from Data.World
    you need to free register there.
    use the built in Desktop connector (Get data and type data.w into search box)

    And you need owner: dataveld
    Dataset Id: wow2021week07
    (small letters)

    The link to the data tells you the secret of why this owner.
    https://data.world/dataveld/wow2021week07

    Nice series of challenges

Comments are closed.

Scroll to Top