2024 Week 6 | Power BI: Customer Lifetime Value Matrix


Welcome back to Workout Wednesday! This week’s challenge is inspired by Ann Jackson, who created the challenge back in 2021 for the Tableau team.

Let’s create a Customer Lifetime Value (CLV) Matrix, a tool that marketers use to segment customers based on their value and loyalty to a business. This helps marketers allocate resources and efforts more efficiently. According to a 2018 survey, 25% of marketers rank CLV among their top 5 metrics. 

This was a new chart type for me to create – a huge thank you to Stephanie Bruno for working through the data and calculations with me!


  Datavizzers – use a pre-built data model

  1. Download .pbix starter file that contains a pre-built data model. This includes all of the calculations you need to build this visual.

  Data wizards – create your data model

  1. Import data from Data.World.
  2. Shape the data in Power Query so that you have everything you need. I created:
    • Date table
      • I used this as an intermediate step to create a disconnected table that included my quarters – you may not need the date table
    • Columns in my Superstore fact table for: Order Quarter and Acquisition Quarter (both include the year)
  3. Create measure(s) for:
    • CLTV (Customer Lifetime Value)
      • the base for this calc is the total sales divided by the number of new customers
      • you’ll need either variables or intermediate measures for: number of new customers, total sales, starter quarter, sales quarter  

  Everyone – create your visuals

  1. Create a matrix with Acquisition quarter on the rows, Quarters since acquisiton on the columns, and your CLTV measure as the values  
  2. Conditionally format your column background and text using the user interface in the formatting pane
    • Note – if you did anything special to make your “New Customers” column, you’ll have to create a new numeric measure that will serve as the value for your formatting
    • You may also need to do a little DAX magic to get the background of the New Customers column to be white   
  3. Add a title and any other design elements that you’d like!
  4. 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.


You can find this week’s data on Data.World

Data.World requires you to create a free account to access the data. You may use the Data.World connector in Power BI or download the file to your local machine and connect to it there.

You can also use the provided .pbix starter file that contains all of the data.


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 File available for download via the data stories gallery.

Scroll to Top