Introduction
This challenge builds upon my last challenge, reusing much of the same logic. Both of them are based on real-world solutions for a client of mine.
In the client scenario, we had used a Sankey chart to show whether customer accounts would grow or shrink over time. This worked great at first to show small accounts weren’t worth investing in.
Later on, that client got push back from the sales team about edge cases. They wanted me detail about exactly what size customers might grow into large customers because those made up a majority of long term sales.
Requirements
- 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.
- 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.
- Create a scatter plot with starting sales for the X axis and ending sales for the Y axis. Use ending size for the legend.
- “Online Store” is an outlier that makes the chart hard to read and doesn’t really represent an individual store. For the whole page, filter out the store name of “Online Store”.
- Using the Analytics pane, add dashed constant lines for the breakpoints of $1,000 and $5,000. Do this for both the X and Y axes. This will break the report into 9 sections corresponding to the 3 sizes of customer: small (<=$1,000), medium (<=$5,000) and large.
- 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:
- Date
- Sales
- 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)