Build a reconciled customer workbook before presenting it in Power BI. Compare revenue, contribution, and retained customers using explicit period rules and totals that tie back to the export.
Customer analysis in Power BI starts with a reconciled customer workbook: consistent revenue periods, agreed contribution costs, and a customer base that retains zero-sales accounts. Build those measures first, then use the report to compare segments without changing their meaning.
A US distributor wants to compare September with October 2026. It asks which region grew revenue, how much contribution remained, and which September customers bought again in October.
Use net merchandise revenue after credits. Contribution means current net revenue less product cost and agreed variable selling and service costs. It is a management measure; record exactly which costs are included. The customer master supplies a stable region assignment for both periods.
Retention here means an opening customer with positive September revenue also has positive October revenue. It is purchase retention across adjacent months, not a declaration that an absent customer has permanently left. Detailed Power BI churn analysis and cohort analysis address those different questions.
Microsoft documents importing Excel data into Power BI Desktop. This workflow calculates the business measures in the workbook before import. Covirage is not affiliated with Microsoft.
Start with invoice and credit exports for both months, product costs, variable customer costs, and the customer master. Match credits to the revenue convention, document unassigned costs, and reconcile each source total before combining it with the customer list.
The customer list must include every ID in either period. A current-month-only export would lose customer B and remove the evidence needed for retention. Keep customer D, a new buyer, but exclude D from the opening retention denominator.
Use these headings in Customers!A1:F5:
| Customer | Region | September revenue (USD) | October revenue (USD) | October product cost (USD) | October variable cost (USD) |
|---|---|---|---|---|---|
| A | West | 1,000 | 1,200 | 720 | 120 |
| B | West | 800 | 0 | 0 | 0 |
| C | East | 600 | 900 | 450 | 150 |
| D | East | 0 | 500 | 300 | 50 |
| Control total | 2,400 | 2,600 | 1,470 | 320 |
The control total belongs outside the imported customer table. Including it as another record doubles amounts when the report sums its rows.
Where costs are allocated rather than directly attributed, keep the allocation policy beside the workbook. A changed allocation rule can change customer contribution without a change in sales or service activity.
Add G for contribution, H for opening customer, I for retained customer, and J for contribution margin. Enter these formulas in row 2 and fill through row 5:
G2 =D2-E2-F2
H2 =--(C2>0)
I2 =--AND(C2>0,D2>0)
J2 =IF(D2>0,G2/D2,"")
A blank margin means there is no positive revenue denominator. It does not erase customer B from the opening base.
| Customer | Contribution (USD) | Opening flag | Retained flag | Contribution margin |
|---|---|---|---|---|
| A | 360 | 1 | 1 | 30.0% |
| B | 0 | 1 | 0 | Not applicable |
| C | 300 | 1 | 1 | 33.3% |
| D | 150 | 0 | 0 | 30.0% |
| Total | 810 | 3 | 2 | 31.2% |
The company calculations are:
=SUM(G2:G5)
=SUM(G2:G5)/SUM(D2:D5)
=SUM(I2:I5)/SUM(H2:H5)
Contribution is 2,600 minus 1,470 minus 320, or 810. Margin is 810 divided by 2,600, or 31.2%. Retention is 2 divided by 3, or 66.7%. Percentages round to one decimal place after calculation.
Revenue rose by 200, from 2,400 to 2,600, while an opening customer stopped buying. The revenue bridge proves why: A added 200, B lost 800, C added 300, and D added 500. Those movements sum to 200. Customer value analysis helps interpret the resulting customer differences.
Create a SegmentSummary sheet. A2 contains West and A3 contains East. Calculate B for revenue, C for contribution, D for opening customers, E for retained customers, F for retention, and G for contribution margin:
B2 =SUMIF(Customers!$B$2:$B$5,A2,Customers!$D$2:$D$5)
C2 =SUMIF(Customers!$B$2:$B$5,A2,Customers!$G$2:$G$5)
D2 =SUMIF(Customers!$B$2:$B$5,A2,Customers!$H$2:$H$5)
E2 =SUMIF(Customers!$B$2:$B$5,A2,Customers!$I$2:$I$5)
F2 =IF(D2>0,E2/D2,"")
G2 =IF(B2>0,C2/B2,"")
| Region | Revenue (USD) | Contribution (USD) | Opening customers | Retained customers | Retention | Contribution margin |
|---|---|---|---|---|---|---|
| West | 1,200 | 360 | 2 | 1 | 50.0% | 30.0% |
| East | 1,400 | 450 | 1 | 1 | 100.0% | 32.1% |
The summary's revenue and contribution must equal the customer sheet. Keep company totals in a separate, single-row CompanySummary table; its retention is 66.7%, calculated from the company counts. Do not sum region percentages.
Format the detail and summary ranges as named Excel tables. In Power BI Desktop, select Home, Get Data, Excel, choose the workbook, select the tables in Navigator, and load them. Microsoft describes these steps in the import guide cited above.
Use a table visualization for exact customer and region comparisons. Display summary percentages without aggregation and turn off totals on that summary visual. Show the checked company table separately.
Microsoft describes slicers as report filtering controls. If you add a region slicer, verify which visuals it filters. The fixed company summary remains an all-company reference; label it accordingly. For the broader reporting approach, see Power BI alternatives.
Bring the customer revenue and cost workbook with its comparison periods. Covirage maps the columns; its tools compute and check the commercial figures, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See customer profitability for the supporting business context.
Include customer ID, the comparison periods, net revenue, agreed direct costs, contribution, and a definition of retained customers. Show counts and dollars alongside rates so readers can distinguish changes in customer numbers from changes in spending.
Yes. New customers or increased spending by continuing accounts can offset customers that stopped buying. Calculate retention only across the opening customer base, then show new-customer revenue separately so growth does not conceal customer losses.
Calculate the group percentage as total contribution divided by total revenue. Averaging customer percentages gives small accounts the same influence as large accounts. Keep the dollar numerator and revenue denominator available in the report for checking.