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

2 thoughts on “2021 Week 23 | Power BI: Can You Build a Cumulative Line Chart?”

  1. Kevin McKenzie

    This has been so interesting and useful for me to watch. I’m new to Power Bi, don’t have a coding background and am isolated working from home with little technical help. Just adding a simple running total in Power Bi was impossible for me, so I just had to do it in Excel.
    With this video I’ve realised I can add my forecast data to my graph, then right click the value and choose ‘New Quick Measure’ and choose ‘Running total’ and it writes the DAX for me. I’ll be honest, I don’t fully understand the DAX it has written, and it’s difficult to get an explanation for it thats easy to understand when I have no prior coding knowledge, but it seems to work ok so that’s the main thing. Thank you.

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