2023 Week 30 | Power BI: Build a smooth line chart

Power BI, Workout Wednesday / By

Introduction

Welcome back to Workout Wednesday! This week we’re building a line chart using the new smoothing feature that was released this month! This week’s challenge was inspired by Andy Kreibel’s Makeover Monday challenge. We’re exploring the disappearing sea ice in the Antarctic using a line chart, looking at the extent of sea ice in millions of square kilometres by year and month. 

The recreation of Andy’s chart, originally created in Tableau, was a fun challenge. We’re using the core visual line chart with some conditional formatting and a neat hack. This week you’ll get a chance to try a new product feature (line smoothing), use DAX and a disconnected table, and create a custom tooltip.

Be sure you’re using the July 2023 version of Power BI desktop or later for this challenge.

Requirements

  Get data

  1.  Use the Data.World connector in Power BI to get data from the Makeover Monday repo.
  2. Only import data for the Southern hemisphere, as this visual is looking at melting sea ice in the Antarctic.

  Create calculated table and measures 

    You’ll need a calculated table and a few measures for this report.

  1. Create two measures for the median value. One for the median value of each month, and another for the median value of ALL months. 
  2. To create the slicer that will highlight the selected year, we’ll need to create a disconnected calculated table. Use the VALUES function to create a table that contains the values from the Year column of the data.
  3. Create a measure to connect your disconnected table to the data using the SELECTEDVALUE function.

These measures might seem a little too abstract without exploring the data and building a simple line chart first. Feel free to reverse the order of the calculations and the visuals, or mix and match!

  Build visuals

  1. Start your visualizations by dropping a line chart on the canvas. You’ll need to use the Years in the legend so that you get one line per year, with months on the x axis.
    • Note: you may notice that you need a month sort column in your dataset to order the months correctly. I like to do this in Power Query and then come back to the visuals.
    • Either use a theme or manually set the colors of your lines.
    • Get your labels, gridlines, and formatting set the way you want it. This is where you have the option to smooth your line!
    • Use the title and subtitle to add context (and to avoid adding another text box visual).
  2. After you’ve created your calculated table, add a slicer to the canvas containing the Year values from your table.
  3. Now it’s time for a hack (thanks to Parker for your post about this!). Copy and paste your line chart, clear the values, and add your measures to your second line chart.
    • The measures I used for the values in this chart are:
      • Median ice extent for the selected year
      • Median ice extent for all years
    • This is most definitely a hack, but it does help us accomplish our goal of replicating Andy’s chart. The main problem with the hack is that because we’re layering one chart over the next, it will not be accessible by screen readers in the way that sighted people see it on the screen. This is a problem that could potentially be alleviated with an update to the core visual (allowing conditional formatting).
  4. Layer your second line chart directly over the first. Use the selection pane to ensure it’s on top.
    •  Format your line colors manually.
  5. Create a report page tooltip to display the values you’d like. I thought it was important to display the median value for all years compared to the median value for the selected year.
  6. Add any other visuals that you’d like!
  7. Ensure that all visuals meet the minimum standards of having alt text, that the tab order in your report makes sense, and that your color choices meet the minimum standards. 

This is only one way to achieve the desired result. We’d love to see any other ways you find to recreate Andy’s chart!

Dataset

This week’s data comes from the Makeover Monday team’s Data.World repo. Note that you only need to import the data for the Southern hemisphere.

You do need to create a Data.World account to access, but it is completely free.

Share

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

Solution File available for download via Data Stories Gallery.

Check out Parker Stevens video, where he shows us a neat hack to dynamically highlight a line chart series.

Scroll to Top