Power BI budget vs actual: how to build a reliable report
Best practice is a report that shows Net Change, Budget Amount and Variance % side by side, built on a stable semantic model with budget versions kept separate. The keys linking actual and budget rows must be identical across all sources. The report must allow drill-down to account level and display a data-through date, so the manager knows how far the figures can be trusted.
In brief:
- Budget comparison in Power BI should use separate views for managers and analysts, to meet their specific needs.
- The data model must be clean, with a star schema, a reliable chart of accounts mapping and a stable key structure that prevents errors.
- Reports should show the key KPIs, such as actual vs budget variances, YTD and forecast, but no more than five of them, so the manager is not overwhelmed.
- Variance % is calculated as the difference divided by a chosen denominator, matched to the relevant context, for example budget or actual totals.
- Automatic data refresh and checks depend on accounts, departments and currencies matching exactly across sources, and resolving errors often requires manual checking.
Contents
- What to show in the report: essential KPIs and the manager and analyst views
- Data model and relationships: account mapping, budget versions and a stable key structure
- Core DAX measures and choosing the percentage denominator
- Implementation, data refresh and financial control processes
- A practical example: how Analitika360 brings a Power BI budget vs actual solution within reach
- Editorial view: when a Power BI budget vs actual solution changes a manager’s work
- How Analitika360 can help sort out your budget comparison
- Sources
- Frequently asked questions
What to show in the report: essential KPIs and the manager and analyst views
Budget comparison in Power BI only works when the needs of the manager and the analyst are split across two separate pages. The manager needs a quick answer on whether everything is going to plan. The analyst needs the reasons.
The manager’s summary must show revenue on an actual and budget basis, costs on an actual and budget basis, EBITDA or net profit, the absolute variance in euros and the variance as a percentage. The Actual vs budget Power BI content states that a minimal manager page should cover actual against budget, the absolute and percentage variance, the year-to-date (YTD) cumulative total and a full-year forecast.
The analyst page needs far more detail:
- a monthly breakdown with the option to compare several periods at once;
- account, department and project views;
- a field for the person responsible for each significant variance;
- a free-text field to record the reason for the variance.
Drill-down from KPI to source transaction is what separates a useful report from a pretty picture. A single annual percentage rarely answers the question ‘why’, so there needs to be a path from the summary figure to the account line and the specific transaction.
Where the budget is tied to quantities rather than just amounts, it is worth showing volume measures separately from monetary ones. The Purchases Actual vs Budget report compares Purchase Amount and Purchase Quantity with their budget equivalents, because a price change and a volume change are two entirely different problems that need to be seen separately.
Pro tip: Do not put more than five KPIs on the manager page. With more measures than that, the manager will stop looking at the summary and go back to the old Excel spreadsheets.
Data model and relationships: account mapping, budget versions and a stable key structure
A reliable budget comparison in Power BI starts not with visuals but with the model architecture. Without a clean star schema, any DAX formula will sooner or later start returning wrong figures.
- Build a star schema. At the centre sits the fact table with transactions, surrounded by Date, Account, Budget/Variant, LegalEntity and other dimension tables.
- Prepare the account mapping before writing any DAX. The path should run from the source account through the financial group and direction (revenue or cost) to the management line shown to the manager. The Ledger budgets Power BI documentation explicitly recommends doing this step before modelling, as otherwise historical budgets have to be recalculated.
- Keep budget versions as separate rows in their own right. The approved budget, the latest estimate and alternative scenarios must not be merged in one table without a clear version flag.
- Ensure stable keys across sources. When actuals come from Rivilė or Finvalda and the budget is held in an Excel or SharePoint file, the account code, period, department and currency must match character for character on both sides.
This work is tedious, but it is exactly what decides whether the report is still reliable six months on or starts showing unexplained differences that nobody can account for.
Core DAX measures and choosing the percentage denominator
The formula itself is simple: Variance = Net Change minus Budget Amount. The percentage is trickier, because Variance % = Variance / chosen denominator, and the choice of denominator changes the whole interpretation.
The Business Central budget comparison content is based on the G/L Account, G/L Entry and G/L Budget Entry tables, where Net Change, Budget Amount, Variance to Budget and Variance to Budget % are the standard measures used as a benchmark.
The choice of denominator depends on the context:
- if costs are seasonal, a percentage of the monthly budget distorts the picture in months when activity is naturally low;
- if the management rule requires comparison with actuals rather than budget, the denominator should be Net Change, not Budget Amount;
- at YTD level, the percentage is better calculated against the cumulative budget, not the monthly average.
A negative variance does not necessarily mean a bad result. On a cost line, actuals below budget are a good sign, whereas on a revenue line it is the opposite. KPI colouring in a Power BI report should follow a rule based on line type, not the same logic for every account.
Pro tip: Create a separate measure for each aggregation level (month, quarter, YTD, year) instead of one all-purpose piece of DAX with lots of IF conditions. Separate measures are easier to test and refresh faster.
Before publishing, every figure must be checked manually against the accounting system for at least one past month. This exposes mapping errors before the manager notices them.
Implementation, data refresh and financial control processes
Implementation follows an orderly sequence that should not be skipped in a rush, as set out in the digital marketing checklist for fintech and SaaS managers:
- Define what counts as actual and what counts as budget, including the exact version.
- Sort out the account and dimension mappings.
- Load the data into the semantic model and check that the keys match.
- Build the DAX measures and test them against the previous month’s data.
- Publish the report with a set refresh schedule and access rights.
The IT Spend Analysis sample shows how plan, actual and latest estimate can coexist in a single report, allowing variances to be explored by time, region and category.
Why the data-through date matters: the report must clearly show the date up to which data has been loaded and whether the month has been closed, because without this marker a manager may make a decision based on an incomplete month, which often leads to a wrong assessment.
Currency conversion and consolidation across departments need separate attention. Documentation on consolidated reports notes that the most common cause of errors is not the DAX formula itself but an unadapted account mapping, or a line changed at the last minute in an Excel file that no longer refreshes in the model.
A practical example: how Analitika360 brings a Power BI budget vs actual solution within reach
Implementing budget vs actual reports for companies whose accounts are kept in Rivilė or Finvalda makes it possible to combine these sources with an Excel or SharePoint budget in a single report that refreshes automatically, with no manual intervention.
A typical result after implementation: the finance manager gets one summary instead of several separate Excel files, and the accountant becomes more efficient. Decisions are based on a single version of the data rather than several different sources that may contradict one another.
Editorial view: when a Power BI budget vs actual solution changes a manager’s work
A report does not replace the manager’s decision, but it does change how the meeting starts. Instead of ‘how much did we spend’, the meeting opens with ‘why did we deviate from plan on the third account’. This demands data discipline: if budget versions are mixed haphazardly, the report just shows the chaos faster. It is worth moving to a specialised solution when Excel can no longer cope with several departments or versions at once.
— Analitika360
How Analitika360 can help sort out your budget comparison
There are ready-made solutions built specifically for Rivilė and Finvalda data that make implementation and customisation easier, unlike generic Power BI templates that take a long time to adapt yourself.
A project typically involves building a semantic model with stable keys, DAX measures based on the chart of accounts, publishing the report and setting up access rights according to usage needs. Data can refresh automatically, reducing the need for manual work. If you would like to see what such a model looks like in practice, take a look at the Power BI report examples or browse the list of solutions by business area and get in touch for a proposal tailored to your company’s accounting system.
Frequently asked questions
Which KPIs are essential in a budget comparison report?
According to the Business Central KPI documentation, the minimal set covers Net Change, Budget Amount, Variance and Variance %. It is also worth showing the YTD total and a full-year forecast.
How do you calculate Variance % in a Power BI report?
Variance is calculated as Net Change minus Budget Amount, and Variance % is obtained by dividing this difference by the chosen denominator, usually the budget or actual total depending on the management rule.
Why should budget versions be kept separate?
The approved budget and the latest estimate answer different questions, so mixing them in one row distorts how the variance is interpreted, as the IT Spend Analysis sample shows.
How long does it take to implement a Power BI budget report?
The time needed depends on the number of data sources and the complexity of the account mapping. Analitika360 agrees the project scope and timescales individually, based on the company’s accounting system.
Can Rivilė and Excel budget data be combined in one report?
Yes, provided both sources use the same keys: account code, period, department and currency. Without this match, the model will produce incorrect reconciliation results.