How to Build a Department Budget Dashboard in PowerBI
Every local government department has a budget. Every department has an obligation to spend that budget effectively — not just avoiding overspending, but ensuring that appropriated funds are fully deployed in service of the residents they were allocated to serve. And in most municipalities, the visibility that department heads need to manage that dual obligation is buried in ERP reports that require a finance degree to interpret and a scheduling request to access.
A PowerBI budget dashboard changes that. Built once, connected to your ERP data source, and refreshed automatically on a defined schedule, it gives every department director a clear, current answer to the questions that actually drive budget management decisions: How much have we spent? How much is committed but not yet paid? How much is genuinely available to redeploy? And are we on track to fully utilize our appropriation before fiscal year end?
This article walks through how to build that dashboard — step by step — with particular attention to the government-specific accounting concepts that make budget dashboards for local government fundamentally different from private sector financial reporting tools.
If you are deciding whether PowerBI or Excel is the right tool for your department's budget reporting, our PowerBI vs Excel comparison for local government covers that decision in detail. This article assumes you have made that choice and are ready to build.
The Government Budget Accounting Concept Most Tutorials Miss
Before writing a single DAX formula, there is a foundational accounting concept that every government budget dashboard must incorporate — and that virtually every generic PowerBI budget tutorial ignores entirely.
In private sector financial reporting, budget dashboards typically show two figures: budget and actual spend. The remaining budget is simply budget minus actual. That calculation works for a business because uncommitted budget is genuinely available.
In government budget accounting, that same calculation produces a misleading number. Local government budgets include a third category — encumbrances — that represents funds that have been formally committed through purchase orders, contracts, or other obligations but have not yet been paid. An encumbrance is not actual spend, but it is not available budget either. It is money that is legally committed and should not be counted as available for redeployment.
A department that has spent $400,000 against a $600,000 budget has $200,000 remaining by a simple budget-minus-actual calculation. But if $150,000 of that remaining balance is encumbered against open purchase orders, only $50,000 is genuinely available for new commitments or transfers. Presenting the $200,000 figure to a department director without the encumbrance context creates false confidence about available resources and can lead to over-commitment of funds that are already spoken for.
This matters operationally because local government budget management involves active fund transfers throughout the fiscal year. When a division has genuinely available budget that will not be needed — a vacancy that was not filled, a capital project that was delayed, a contract that came in under estimate — that surplus can be transferred to departments or programs where funds are most needed to serve residents. The decision about whether and how much to transfer depends entirely on knowing the true available balance — which requires subtracting encumbrances from the remaining budget, not just actual spend.
Your PowerBI budget dashboard must show three budget positions: amount spent, encumbrances outstanding, and true remaining budget. Any dashboard that shows only two of these three is showing an incomplete and potentially misleading picture.
Step 1: Understand Your Data Source
Government budget data lives in your ERP system — Tyler Munis, SAP, Oracle, or another platform depending on your jurisdiction. Before building anything in PowerBI, understand what data your ERP can export and in what format.
Most government ERP systems can produce budget reports at the account line item level that include four key fields: appropriated budget amount, year-to-date expenditures, outstanding encumbrances, and available balance. The available balance in most ERP reports is already calculated as appropriation minus expenditures minus encumbrances — which is the correct government accounting formula.
Your data export should ideally include these columns for each account line:
Department — the organizational unit the budget line belongs to. This is your primary filter dimension — every view on your dashboard should be filterable by department.
Fund — the funding source. General Fund, Water Fund, Capital Projects Fund. Important for dashboards that serve multiple fund types.
Account Code — the chart of accounts identifier for each budget line.
Account Description — the plain-language description of what the account funds. This is what your department heads actually read — not the account code.
Category — the broad expenditure category. Personnel Services, Operating, Capital Outlay. Essential for the spending pattern analysis that tells directors whether their personnel budget is on track separately from their operating budget.
Appropriated Budget — the total approved spending authority for the account in the current fiscal year, including any amendments.
Expenditures YTD — actual payments made against the account year to date.
Encumbrances — committed but unpaid obligations.
Available Balance — appropriated budget minus expenditures minus encumbrances.
Fiscal Period — the month within the fiscal year. Essential for trend analysis and pace tracking.
If your ERP system can export this structure to Excel or CSV monthly, you have everything you need to build a PowerBI dashboard that updates with each new export.
Step 2: Connect Your Data to PowerBI Desktop
Open PowerBI Desktop and click Get Data in the top ribbon. Select Excel Workbook or Text/CSV depending on your export format. Navigate to your ERP export file and load it.
In the Power Query Editor that opens, verify your column types before proceeding:
- Department, Fund, Account Code, Account Description, Category: Text
- Appropriated Budget, Expenditures YTD, Encumbrances, Available Balance: Decimal Number
- Fiscal Period: Whole Number or Date depending on your export format
If any numeric column imported as text — which happens when ERP exports include dollar signs, commas, or parentheses for negative numbers — use the Transform tab to clean and retype those columns before loading. A budget column that PowerBI reads as text will produce blank or error values in every calculation.
Click Close and Apply once your columns are correctly typed.
Step 3: Build Your Core DAX Measures
With your data loaded, create the following DAX measures in the Fields pane. Right click your table name and select New Measure for each one.
Total Appropriated Budget
Total Budget = SUM('BudgetData'[Appropriated Budget])Total Expenditures YTD
Total Spent = SUM('BudgetData'[Expenditures YTD])Total Encumbrances
Total Encumbrances = SUM('BudgetData'[Encumbrances])True Available Balance
Available Balance = SUM('BudgetData'[Appropriated Budget])
- SUM('BudgetData'[Expenditures YTD])
- SUM('BudgetData'[Encumbrances])Percent of Budget Expended
Pct Expended =
DIVIDE(
SUM('BudgetData'[Expenditures YTD]),
SUM('BudgetData'[Appropriated Budget]),
0
)Percent of Budget Committed (Spent + Encumbered)
Pct Committed =
DIVIDE(
SUM('BudgetData'[Expenditures YTD]) + SUM('BudgetData'[Encumbrances]),
SUM('BudgetData'[Appropriated Budget]),
0
)Budget Variance (Available as % of Total)
Available Pct =
DIVIDE(
[Available Balance],
SUM('BudgetData'[Appropriated Budget]),
0
)The distinction between Pct Expended and Pct Committed is operationally important. Pct Expended shows what has been paid. Pct Committed shows what has been paid plus what is legally obligated. For a department director evaluating whether funds are available for transfer, Pct Committed is the correct figure — it shows the true deployment rate of appropriated funds.
Step 4: Build Your Dashboard Layout
Create a new report page in PowerBI Desktop. Your budget dashboard layout should have four sections organized from most important to most detailed, top to bottom.
Section 1 — Department Filter (top of page)
Add a Slicer visual connected to your Department field. Set it to single-select mode. This slicer controls every other visual on the page — when a director selects their department, every KPI card, chart, and table updates to show only their data.
Add a second slicer for Fiscal Period — formatted as a dropdown — so the director can view the current month, the current quarter, or year to date.
Section 2 — KPI Cards (three cards across the top)
Add three Card visuals displaying your three headline budget positions:
Card 1: Total Spent — formatted as currency. Label it "Expenditures YTD."
Card 2: Total Encumbrances — formatted as currency. Label it "Encumbrances Outstanding." This is the card that most government budget dashboards omit and the one that makes yours operationally accurate.
Card 3: Available Balance — formatted as currency. Label it "True Available Balance." Apply conditional formatting — red background when the available balance is negative, green when positive. A negative available balance means the department is over-committed and needs immediate attention.
Section 3 — Budget Utilization Visual (center of page)
Add a Clustered Bar Chart showing Total Budget, Total Spent, and Total Encumbrances side by side for each account Category — Personnel Services, Operating, Capital Outlay. This view immediately tells a department director whether their personnel budget is tracking differently from their operating budget, which is the most common pattern that requires management attention during the fiscal year.
Add a Card showing Pct Committed — formatted as a percentage — with a reference label showing the percent of fiscal year elapsed. When Pct Committed is significantly below percent of year elapsed, the department is at risk of a year-end appropriation lapse. When Pct Committed approaches or exceeds 100%, the department needs to review encumbrances for accuracy and potential over-commitment.
As discussed in our budget transparency dashboard guide, the comparison between percent of budget committed and percent of fiscal year elapsed is the single most operationally useful budget metric in local government — and it requires the encumbrance data that most dashboards omit.
Section 4 — Account Line Item Detail Table (bottom of page)
Add a Table visual showing each account line with columns for Account Description, Appropriated Budget, Expenditures YTD, Encumbrances, Available Balance, and Pct Committed. Sort by Available Balance ascending — accounts with the lowest available balance appear first, which immediately surfaces the lines that need attention.
This table is where department heads and finance officers do the detailed review that the KPI cards and charts flag as necessary. A director whose overall available balance looks healthy may have one or two account lines that are significantly over-committed — the table surfaces those immediately.
Step 5: Add Conditional Formatting
Conditional formatting transforms your budget table from a data grid into a management tool. Apply the following formatting rules to the Available Balance and Pct Committed columns in your detail table:
Available Balance column:
- Negative values: red background, white text — over-committed
- Zero to 5% of budget: amber background — approaching zero
- Greater than 5% of budget: no special formatting — normal
Pct Committed column:
- Greater than 100%: red — over-committed
- 90% to 100%: amber — approaching full utilization
- Less than 90% relative to percent of year elapsed: light blue — potentially under-spending
Right click on the column in the visual, select Conditional Formatting, choose Background color, and define your rules using the Pct Committed measure as the basis. PowerBI's conditional formatting interface handles this without any additional DAX.
Step 6: Configure Data Refresh
A budget dashboard that requires manual data loading to update is only marginally better than the ERP report it replaces. Configure your data refresh to minimize manual intervention.
If your ERP system saves its budget export to a consistent file path — the same folder, the same filename — every month, PowerBI Desktop can reconnect to that file automatically when you open the workbook. The convention of saving the current month's export to a fixed filename like budget_export_current.xlsx and overwriting it each month means opening the PowerBI file and clicking Refresh is the only manual step required.
For departments with access to PowerBI Service and sufficient licensing, publishing the report to the cloud and configuring an automatic scheduled refresh eliminates even that manual step — the dashboard updates on whatever schedule you configure, with no human intervention required after setup. As discussed in our PowerBI vs Excel comparison, this automated refresh capability is one of PowerBI's primary advantages over Excel for recurring executive reporting.
Making the Dashboard Work for Non-Technical Audiences
Department heads who will use this dashboard daily are not data analysts. They need to extract actionable insight in under thirty seconds. Three design principles make the difference between a dashboard people check every week and one they open once and ignore.
Lead with the exception, not the summary. The most important thing a department director needs to know is whether anything requires their attention right now. Place your conditional formatting on the KPI cards so that a director opening the dashboard immediately sees red, amber, or green — and knows within five seconds whether they need to read further.
Use plain language everywhere. "Available Balance" not "Unencumbered Appropriation." "Amount Spent" not "Expenditures YTD." "Funds Committed" not "Encumbrances." Your audience manages departments — they do not work in accounting. The dashboard should speak their language, not the ERP system's language.
Keep it to one page. A budget dashboard that requires navigation between pages to get the full picture will not be used by non-technical department heads. Everything a director needs to manage their budget — the three KPI cards, the utilization visual, and the line item table — fits on one well-designed page. If it does not fit, the design needs to be simplified, not the page count increased.
What This Dashboard Enables That ERP Reports Cannot
The ERP budget report and the PowerBI dashboard contain the same underlying data. What is different is how accessible that data is to the people who need to act on it.
An ERP budget report requires someone to log into the system, navigate to the right report, select the correct parameters, wait for it to generate, and interpret a dense tabular output that was designed for accountants. A department director who is not technically sophisticated — or who simply does not have time — is unlikely to do that consistently.
A PowerBI dashboard requires clicking a bookmark. The department filter is already set to their department. The current month is already selected. The red and amber flags are already visible. The decision about whether action is needed is apparent in five seconds rather than five minutes.
That accessibility difference changes behavior. Department heads who can see their budget position instantly check it more often — and catch developing problems earlier. Finance officers who can view all departments simultaneously on a single report make fund transfer decisions with better information. The dashboard does not change the underlying budget math. It changes who can see it, how often, and how quickly they can act on what they see.
This is the practical governance value of a well-built government budget dashboard — and it is why 84% of UK councils now use PowerBI as their primary data analytics platform.
Final Thoughts
Building a department budget dashboard in PowerBI is a three to four hour project for an analyst already familiar with the tool — and a half-day project for someone building their first PowerBI report from the walkthrough above. The investment is made once. The return compounds across every subsequent budget cycle when department heads and finance officers are managing from current, accurate data rather than requesting reports and waiting.
The encumbrance concept is the detail that makes this dashboard genuinely useful for government rather than superficially similar to a corporate financial tool. Include it. Your department directors will notice — and your finance officers will trust the numbers.
Want to start with Excel before moving to PowerBI?
If your department is not yet ready for PowerBI, the Local Government Dashboard Template Pack includes a fully built budget performance dashboard in Excel — the same encumbrance-aware three-position layout described in this article, pre-loaded with fictional data and ready to connect to your ERP export in under 30 minutes.
