2021 Week 23 | Power BI: Can You Build a Cumulative Line Chart?

Introduction

Continuing with the Nobel Prize data that Meagan began exploring last week, this week extends to consider aspects of individual Nobel laureates. The main visual is a cumulative line chart, but you’ll also provide detail on laureates with a report page tooltip.

While additional JSON expansion and transformation was required from the original dataset, this week goes a bit easier on Power Query and provides a relatively clean (but not completely clean) dataset posted to Data.World.

The challenge this week builds upon some basic DAX seen in prior challenges, optionally explores Quick Measures, and then requires you to use DAX to create a measure where it might initially be tempting to create a separate column.

Using the countries of birth (or modern equivalents provided in the “now” column), this challenge allows you to better explore the geographical diversity of laureates over time.

The text on the embedded version is difficult to read, so use the arrow icon in the lower right corner to expand to full screen.

Requirements

  • Create a measure that provides a distinct count of Laureates (there are individuals who have won multiple awards)
  • Create a measure that provides a running total of laureates by award year (HINT: there’s a Quick Measure)
  • Create a measure named “Age When Awarded” that roughly calculates age based on Award Year minus Birth Year.
    • Do NOT create this as a column. The challenge is to create the Age calculation using only a measure.
    • Account for a 2020 award winner who does not have a birth date listed without filtering out the record.
    • It’s not a perfect calculation for someone’s age, but we’re not trying to win any prizes here…
  • Add a line chart showing cumulative laureates over time by continent
  • Add a zoom slider to the line chart’s Y axis
  • Add a report page tooltip containing a table showing data for each individual laureate (NGOs and other non-individual entities can be excluded). Add your “Age When Awarded” measure to this table.

Dataset

This week uses a prepared version of the Nobel dataset available on Data.World. Try the built-in Power BI connector for Data.World! Like Meagan’s data from last week, its original source is the Nobel Prize API.

https://data.world/dataveld/nobel-laureates
Owner: dataveld
Dataset: nobel-laureates

Share

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

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top