2024 Week 6 | Power BI: Customer Lifetime Value Matrix

  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.