Power BI Project · Advanced · 4 to 6 hours
Customer cohort and retention DAX in Power BI
New vs returning, repeat rate, AOV, and share of segment, built with SUMX, DISTINCTCOUNT, and CALCULATE with ALL. The measures that separate row context from filter context.
The brief
A direct-to-consumer brand has 842 orders from 420 customers and wants to understand repeat behavior: how many orders come from brand-new customers vs returning ones, what share of customers ever buy again, and which segment actually drives revenue. Each answer is a DAX measure that forces you to reason about context: new vs returning needs row context inside an iterator, share of segment needs CALCULATE with ALL to expose the grand total, and repeat rate is a customer-level metric that a naive order-level count will get wrong.
Your role
You are the People-and-Growth analyst. Deliverable: a one-page Power BI report with new-vs-returning, repeat rate, AOV, and segment revenue, plus your top 3 findings.
The dataset
842 orders (Jan 2025 to Jun 2026) from 420 customers, with signup date on each row
powerbi-customer-orders.csv · 842 rows
Columns: order_id, customer_id, customer_signup_date, region, segment, order_date, amount
Setup
- Open Power BI Desktop (free) and load powerbi-customer-orders.csv.
- Note that customer_signup_date is denormalized onto every order row.
- Confirm order_date and customer_signup_date read as dates and amount as a number.
Your tasks
Work through these in order, the way the engagement would actually run.
- 1Write Total Revenue, Orders (DISTINCTCOUNT of order_id), and Customers (DISTINCTCOUNT of customer_id).
- 2Write AOV as DIVIDE(Total Revenue, Orders) and confirm it against the total.
- 3Define a first-order-date measure per customer, then a New Orders measure where order_date equals that first date.
- 4Derive Returning Orders as total orders minus new orders, and validate the two sum to the total.
- 5Write Repeat Rate as customers with more than one order divided by total customers, a customer-level metric.
- 6Write Segment Share of Revenue using CALCULATE(Revenue, ALL(segment)) as the denominator.
- 7Assemble the report and write your top 3 findings with the numbers behind them.
Work like an AI-powered analyst
The modern analyst uses AI as a thinking partner, not a shortcut that skips the learning. Try these on this project.
- Ask ChatGPT or Claude why new vs returning needs an iterator with row context, and why plain SUM cannot express it.
- Have the AI explain why ALL(segment) is required in the denominator for a share-of-total measure, with a one-line example.
- Paste your repeat-rate measure and ask the AI to confirm it counts customers, not orders.
Expected output
- A one-page report with new-vs-returning, repeat rate, AOV, and segment revenue.
- A new-orders measure defined by first-order date, validated against the total.
- A repeat rate computed at the customer level.
- Three findings, each backed by a specific number.
Check your numbers
Your results should match these. If they do not, that is the real learning: find out why.
- The data is 842 orders from 420 customers, about 1.02 million pesos in revenue, an AOV of about 1,210 pesos.
- 216 of 420 customers ordered more than once, a repeat rate of 51.4 percent.
- New orders (each customer's first) total 420 and returning orders total 422, and the two must sum to 842.
- Reseller is the top segment by revenue at about 343,275 pesos.
- Because signup date is not always the first order date, defining new by first order (not by signup) is what keeps the split honest.
Skills check
6 advanced questions on the judgement this project demands, not just its steps. Your score saves to your dashboard, and you can retry for a better one.
Finished it? Put it in your portfolio.
This is exactly the kind of output the bootcamp builds with you live, with mentor feedback and an AI badge and certificate of completion at the end.