Week 13 | Power BI: Segments and Sanky

Power BI, Workout Wednesday / By

Introduction

I’m honored to be added to the team and I’m expecting I’ll be learning as much about dataviz as you are all will. This first challenge comes from the real world, where a simple visual shift made a big difference for a small company.

The question from the customer was simple: do new customer accounts grow over time or stay small. There was a disagreement about where sales should spend their time investing. If they only care about large accounts, should they only try to land large accounts, or could accounts that start small be grown into large ones.

In the past we used simple tables to try to show this, but it was hard to read and explain. But when we switched to a simple Sankey diagram, it got the point across enough to convince the sales people.

Requirements

  1. Create two dax columns or measures that shows the total amount of sales (sum of NetPrice) for each individual store. One for the first 180 days of sales (based on the first sales date for that store) and the other for the last 180 days of sales for that particular store. Therefore, these start and end dates will differ for each store based on Order Date in the Sales table.
  2. Based on those sales amounts segment each store into small (<= $1,000), medium (<=$5,000), or large. For stores with no sales, return BLANK() as the segment. Do this for both starting size and ending size.
  3. Create a Sankey Diagram that show how store size changed from start to finish based on those segments/bins. Filter out blank sizes for stores with no sales.
  4. Finally, create a detailed table that shows Store name, Sales for the first 180 and last 180 days, and starting and ending size.

Dataset

For this challenge you will use the Contoso 10k generated dataset (file csv-10k.7z) provided by SQLBI. You may need to install 7-zip to open a 7z file.

There are 3 tables needed for this exercise:

  1. Date
  2. Sales
  3. Store

Share

After you finish your workout, share on Bluesky or LinkedIn using the hashtags #WOW2025 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko (on BlueSky) or tag the author Eugene (@sqlgene.com)

Solution

Via Data Stories Gallery 

Scroll to Top