Power BI customer profitability analysis beyond gross margin
Power BI customer profitability analysis works out what each customer earns for the business after product cost, discounts and the cost of serving them. Large accounts do not always come out on top, because freight, returns and support can eat the margin. A small team builds the model in your own Microsoft tenant, and the allocation rules stay visible.
- Margin after cost to serve
- Allocation rules in tables
- Accounts ranked by profit
Last updated
Why does Power BI customer profitability analysis matter?
Revenue rankings flatter big accounts. A customer may buy a great deal and still earn the company little. They negotiate deep discounts, order in small drops, return often and pay late. None of that shows on a sales report.
Profit by customer brings those costs into view. First it takes revenue and removes the cost of the goods. Then it removes the costs that belong to that customer alone. What remains is the contribution the account makes.
The result often surprises people. Some mid-sized accounts turn out to be the most valuable, while a few famous names lose money. So account managers gain facts for the next price or terms conversation.
Cost layers between revenue and customer profit
The page shows each layer as its own step, so nobody has to take the final number on faith.
Net revenue
Start from invoiced sales, then subtract discounts, rebates and credit notes for the customer.
Gross margin
Subtract the cost of the goods or services sold. This is where most sales reports stop.
Freight and delivery
Add the shipping cost the company absorbs. Since small frequent orders cost more to ship, they lower the margin.
Returns handling
Charge the cost of processing returns to the customer who sent them back.
Service and support
Assign support effort by tickets or hours logged against the account, when your systems record them.
Payment terms
Estimate the financing cost of slow payment from days outstanding. Finance supplies the rate, and we label the figure as an estimate.
How do you allocate shared costs to customers in Power BI?
Some costs arrive with a customer code attached, such as a freight invoice for one delivery. Those are easy. Others cover many customers at once, for example a warehouse team or a support desk. They need a driver.
A driver is a fair basis for sharing a cost. Order lines may drive picking cost, and ticket counts may drive support cost. We hold each rule in a table with the cost pool, the driver and the period. Then a measure spreads the pool across customers in proportion to the driver. Writing those allocation measures well is typical power bi dax consulting work.
No allocation is perfectly true, so openness matters more than precision. Therefore the report shows directly traced costs and allocated costs in separate columns. If a manager disputes a driver, finance can change the table and compare the two results.
Mistakes in Power BI customer profitability analysis
These six faults lead to numbers that account managers reject.
- Stopping at gross margin and calling it profit
- Spreading every shared cost by revenue alone
- Hiding allocation rules inside long formulas
- Ignoring rebates that finance books at year end
- Treating parent and child accounts as unrelated
- Judging a customer on a single unusual month
How we deliver Power BI customer profitability analysis
Finance and sales both sign off the rules before any ranking goes public.
List the cost pools
First finance names the costs to include and marks which ones carry a customer code already.
Choose the drivers
We propose a driver for each shared pool and check that the driver data exists at customer level.
Build and reconcile
Next we build the model and confirm that customer profit adds back up to the company's reported margin.
Review with sales
Finally account managers review their accounts, and we adjust obvious data errors before the wider release.
Customer profitability in Power BI: questions from finance
What data does Power BI customer profitability analysis need?
It needs invoice lines with cost, plus any discounts, rebates and credit notes. Beyond that, each cost to serve needs a source: freight invoices, return records, support tickets or payment history. However, you do not need all of them on day one. We often begin with margin and freight, then add layers over time.
How accurate is customer profitability in Power BI?
Directly traced costs are as accurate as your source systems. Allocated costs are estimates, and their quality depends on the driver. So we separate the two on the page. The goal is a fair ranking and a sound basis for decisions. It is not a statutory figure, and we say that plainly in the report notes.
Can the report show which customers lose money?
Yes. A ranked table and a cumulative profit curve both show this. The curve climbs while profitable customers add up, then falls as loss-making ones subtract. As a result, leaders see how much profit the weakest accounts remove. From there, a click opens the cost layers for any single account.
Will account managers see each other's customers?
Only if you want that. Row-level security can limit each manager to their own accounts, while sales leaders see all of them. The rule reads from an ownership table that your team maintains. Because cost data is sensitive, we also agree which cost layers each audience may open.
Who are you, and how do we start?
Power BI Agency is a division of Sataware Technologies and is independent of Microsoft. Our consultants deliver the work online. To start, book the free 30-minute assessment. We answer within 2 hours and then send a fixed scope in writing.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.