Power BI variance analysis that explains the gap to plan
Power BI variance analysis explains why an actual result differs from a plan, a forecast or a prior period. It splits the gap into causes such as price, volume, mix and cost, and then lets a reader open each one. A total alone starts an argument, while a split starts a decision. Our consultants build the measures in your own Microsoft tenant.
- Price, volume and mix
- Waterfall bridges
- Drill to the cause
Last updated
What does Power BI variance analysis explain?
Every finance review hits the same moment. Revenue came in under plan, and someone asks why. Without a split, the room guesses. One person blames price, another blames a lost customer, and the meeting runs long.
A variance page replaces the guess. It shows how much of the gap came from selling fewer units, how much from a lower price and how much from a different product mix. So each cause has a number and an owner.
The same idea applies to cost. For example, a spending overrun may come from rate, from usage or from timing. When the page separates those, a manager knows whether to renegotiate, to cut back or simply to wait.
Variance types and the question each one settles
These are standard management accounting splits, written as measures in the model.
Volume variance
The change in units at the planned price and margin. It shows what selling more or less alone did.
Price variance
The change in price on the units sold. So a discount habit becomes visible as money.
Mix variance
The effect of selling a different blend of products or customers, because rich and lean items rarely move together.
Cost rate variance
The difference between the planned and the real cost per unit or hour. Then purchasing sees its share.
Timing variance
Spend that arrived in a different month than planned. As a result, a gap that will reverse stands apart.
Currency effect
The part of the gap that comes from exchange rates, while the business result underneath stays readable.
Which visuals and data make variance clear in Power BI?
Two built-in visuals do most of the work. A waterfall chart walks from plan to actual, one cause at a time. A decomposition tree lets the reader split a gap by any dimension they choose, such as region, then product, then customer.
The data needs matching detail on both sides. Price, volume and mix require units and amounts in the actuals and in the plan. However, many budgets hold amounts only. In that case the page can still show the gap by line and owner, and we say plainly which splits the data cannot support.
Sign rules also matter. A cost under plan is good, while revenue under plan is bad. So we store a favorable or unfavorable flag per account. Then colors mean the same thing on every row. Price, volume and mix splits need careful measures, and power bi dax consulting covers that work.
Mistakes that mislead in Power BI variance analysis
Each of these produces a chart that looks right and reads wrong.
- Using one color rule for revenue and cost
- Calculating mix at too high a level
- Comparing actuals with a stale plan version
- Showing rates of change on tiny bases
- Leaving timing differences inside real overruns
- Letting each report define variance its own way
How we deliver Power BI variance analysis
You see working measures each week, so the method stays open to challenge.
Agree the method
First we write down the formulas for each split, because several valid conventions exist.
Check the data grain
Next we confirm which plan and actual fields exist at unit level. The quote states what that allows.
Write shared measures
Then we build the measures once in the model, so every page reuses the same logic.
Prove and publish
Finally the splits must add back to the total gap for closed months before anyone else sees them.
Variance reporting in Power BI: questions from finance
Can Power BI variance analysis split price, volume and mix?
Yes, when both actuals and the comparison hold units and amounts at product level. The measures work out each effect and the three add back to the total gap. Several conventions exist for the mix term, so we agree one with your finance lead first. Then the definition appears on the page for every reader.
What is a waterfall chart in Power BI used for in variance work?
A waterfall chart shows a starting value, a series of increases and decreases, and an ending value. In variance work it starts at plan and ends at actual. Each bar between them is one cause. Because the bars float, readers see at once which causes pushed the result up and which pulled it down.
How is Power BI variance analysis different from a budget report?
A budget report shows the gap per line. Variance analysis explains it. So the first tells you travel ran over, while the second tells you whether rates or trip counts caused that. Most finance teams want both. The budget page acts as the entry point, and the analysis page opens from it.
Can we compare against last year instead of budget?
You can. The measures take a comparison basis as an input, so the same page can switch between budget, forecast and prior year. A slicer or a field parameter makes that choice for the reader. However, prior-year splits need last year's units, so we check that history first.
How do we get started?
Call (480) 485-7373 or send a message. We reply within 2 hours and begin with a free 30-minute assessment. Power BI Agency is a division of Sataware Technologies, independent of Microsoft. Our consultants deliver the work online, in your tenant, with a demo each week.
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.