Power BI budget vs actual dashboard with Copilot and DAX variance measures

48 minutes ago

9 min read

A budget vs actual dashboard in Power BI rests on four measures: actual sales, budget amount, variance in dollars and variance as a percentage of budget. Copilot in Power BI can draft those measures as a DAX query, explain them and generate a report page around them, but it works only from the semantic model's metadata, so the result is as good as the model and the checks you run on it. The budget table is the usual weak point, because budgets are set by month and by category while sales arrive by day and by product.

Microsoft's modeling guidance handles that mismatch with a measure that returns BLANK whenever a visual drills below the grain of the budget. Copilot does not apply that rule unless you ask for it, so the measures below come with a guard condition and a worked example whose correct values were computed in Python.

All requirements and menu names here were checked against Microsoft Learn documentation on November 2, 2026. Copilot in Power BI needs a workspace on a paid Fabric capacity (F2 or higher) or Power BI Premium capacity (P1 or higher), and Microsoft states that a Pro or Premium Per User license alone is not sufficient.

··········

POWER BI BUDGET VS ACTUAL MODEL: TWO FACT TABLES AT DIFFERENT GRAIN.

The model has two dimension tables, Date and Product, and two fact tables, Sales and Budget. Sales holds one row per transaction with a date, a product key and an amount. Budget holds one row per month and category, with the month stored as a date value equal to the first day of the month, which is the convention Microsoft recommends for facts recorded at a higher time grain than day.

Two relationships connect the Budget table. Date[Date] relates one-to-many to Budget[MonthStart], because every month start is a valid date in the date table. Product[Category] relates to Budget[Category] as a many-to-many relationship with a single filter direction, from Product to Budget, because category values repeat on both sides and no "one" side exists. Microsoft also advises hiding the summarizable budget column so that report authors and Copilot reach the budget only through a measure.

That measure carries the guard. Without it, a matrix with dates on rows shows the whole January budget on January 1 and BLANK on every other day, and a product-level visual repeats the category budget for every product in the category. Microsoft's guidance is to return BLANK when lower-level dimension columns filter or group the visual, using the ISFILTERED function. If you already work through budget variances in spreadsheets, the same logic appears in running a budget vs actual variance analysis with Copilot in Excel; in Power BI it has to live in the model.

··········

COPILOT IN POWER BI REQUIREMENTS FOR DAX AND REPORT PAGES.

Two Copilot features do the work: Copilot in DAX query view writes and explains DAX queries, and Copilot in the report editor creates and edits report pages. Both depend on capacity and tenant settings that an administrator controls.

........

Copilot feature	Documented requirement
Any Copilot in Power BI	Paid Fabric F2+ or Premium P1+ capacity, or a Fabric Copilot capacity
Trial capacity	Not supported; only paid SKUs
Pro or PPU license alone	Not sufficient for Copilot
Tenant switch	Users can use Copilot and other features powered by Azure OpenAI
DAX queries in Desktop	Preview feature "DAX query view with Copilot" enabled
DAX queries in the service	Write permission on the semantic model; workspace web editing setting on
Report pages	Edit access or build access; Q&A switch on; implicit measures not disabled

........

In Power BI Desktop you also need admin, member or contributor access to at least one workspace on a qualifying capacity, and Copilot asks you to pick that workspace on first use. Capacity cost is a separate decision from Copilot itself, and the license math is set out in the comparison of Power BI Pro, Premium Per User and Fabric F64 capacity. If Azure OpenAI is not available in your capacity's region, an administrator has to accept cross-geo processing before Copilot works, and Microsoft states that Copilot is not currently supported for Private Link or closed network environments.

··········

ILLUSTRATIVE BUDGET AND ACTUAL DATA FOR Q1 2026.

The example uses three categories over the first quarter of 2026. The figures are illustrative and invented for this example. The actual column is the sum of the day-level Sales rows for each month and category, which is what the Actual Sales measure returns at that grain.

........

Month start	Category	Budget (USD)	Actual (USD)
2026-01-01	Hardware	120,000	126,400
2026-01-01	Software	80,000	74,800
2026-01-01	Services	50,000	51,300
2026-02-01	Hardware	110,000	104,500
2026-02-01	Software	85,000	88,900
2026-02-01	Services	52,000	49,700
2026-03-01	Hardware	130,000	138,200
2026-03-01	Software	90,000	86,100
2026-03-01	Services	55,000	58,900

........

The date table has a Date column and a Month Start column, and the product table has Product and Category columns. Descriptive names matter more than usual here, because Copilot reads table names, column names, measure names and the first 200 characters of each description, and it cannot see the rows of the tables beyond minimum and maximum sample values in import mode.

··········

WRITING THE VARIANCE MEASURES WITH COPILOT IN DAX QUERY VIEW.

In Power BI Desktop, DAX query view is the fourth view on the left side of the window. Create a new query tab and open Copilot with the Copilot button or CTRL + I. In the Power BI service, choose the semantic model in the workspace, select Write DAX queries from the context menu and open Copilot the same way. A prompt that names the tables, the grain rule and the sign convention gives Copilot the context the metadata alone does not carry.

Write a DAX query that defines four measures and shows them by 'Date'[Month Start]
and 'Product'[Category] for January to March 2026:
1. Actual Sales = sum of Sales[Amount]
2. Budget Amount = sum of Budget[BudgetAmount], returning BLANK when 'Date'[Date]
   or 'Product'[Product] is filtered, because the budget is set by month and category
3. Variance = Actual Sales minus Budget Amount, BLANK when there is no budget
4. Variance % = Variance divided by Budget Amount, using DIVIDE

Run the generated query with Run or F5 before you select Keep query. Compare it with the reference query below, which follows Microsoft's ISFILTERED pattern and uses DIVIDE so that a missing or zero budget returns BLANK instead of an error.

DEFINE
    MEASURE Sales[Actual Sales] = SUM ( Sales[Amount] )
    MEASURE Budget[Budget Amount] =
        IF (
            NOT ISFILTERED ( 'Date'[Date] )
                && NOT ISFILTERED ( 'Product'[Product] ),
            SUM ( Budget[BudgetAmount] )
        )
    MEASURE Sales[Variance] =
        IF (
            NOT ISBLANK ( [Budget Amount] ),
            [Actual Sales] - [Budget Amount]
        )
    MEASURE Sales[Variance %] = DIVIDE ( [Variance], [Budget Amount] )
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Month Start],
    'Product'[Category],
    "Actual Sales", [Actual Sales],
    "Budget Amount", [Budget Amount],
    "Variance", [Variance],
    "Variance %", [Variance %]
)
ORDER BY 'Date'[Month Start], 'Product'[Category]

Measures written in the DEFINE MEASURE block exist only for the query until you add them to the model. DAX query view shows an "Update model: Add new measure" CodeLens above each measure that is not yet in the model, and the Update model with changes button in the command bar adds or overwrites all of them at once. Explain this query on Copilot's version gives a plain-language description to compare with the four rules in your prompt.

A year-to-date budget needs one more measure. DATESYTD applies a filter directly to 'Date'[Date], so wrapping the guarded Budget Amount measure inside it would trip the ISFILTERED test and return BLANK. The YTD budget therefore sums the column directly.

DEFINE
    MEASURE Budget[Budget YTD] =
        CALCULATE ( SUM ( Budget[BudgetAmount] ), DATESYTD ( 'Date'[Date] ) )
    MEASURE Sales[Actual YTD] =
        CALCULATE ( [Actual Sales], DATESYTD ( 'Date'[Date] ) )
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Month Start],
    "Budget YTD", [Budget YTD],
    "Actual YTD", [Actual YTD]
)

··········

EXPECTED BUDGET VS ACTUAL RESULTS TO CHECK COPILOT AGAINST.

These are the correct values for the illustrative data, computed in Python with pandas. Variance is actual minus budget, so a positive figure means sales above budget. Percentages are rounded to one decimal place.

........

Month start	Category	Variance (USD)	Variance %
2026-01-01	Hardware	6,400	5.3%
2026-01-01	Software	-5,200	-6.5%
2026-01-01	Services	1,300	2.6%
2026-02-01	Hardware	-5,500	-5.0%
2026-02-01	Software	3,900	4.6%
2026-02-01	Services	-2,300	-4.4%
2026-03-01	Hardware	8,200	6.3%
2026-03-01	Software	-3,900	-4.3%
2026-03-01	Services	3,900	7.1%

........

The subtotals are where an incorrect measure usually shows itself. The quarter's budget is 772,000 USD against actual sales of 778,800 USD, a variance of 6,800 USD or 0.88% of budget.

........

Level	Budget (USD)	Actual (USD)	Variance (USD)	Variance %
January	250,000	252,500	2,500	1.0%
February	247,000	243,100	-3,900	-1.6%
March	275,000	283,200	8,200	3.0%
Hardware Q1	360,000	369,100	9,100	2.5%
Software Q1	255,000	249,800	-5,200	-2.0%
Services Q1	157,000	159,900	2,900	1.8%
Q1 total	772,000	778,800	6,800	0.88%

........

The year-to-date query should return a budget of 250,000, 497,000 and 772,000 USD for January, February and March, against actual sales of 252,500, 495,600 and 778,800 USD. February is the month to check, because the cumulative variance there is negative (-1,400 USD) while January and March are positive.

import pandas as pd

budget = [120000, 80000, 50000, 110000, 85000, 52000, 130000, 90000, 55000]
actual = [126400, 74800, 51300, 104500, 88900, 49700, 138200, 86100, 58900]
df = pd.DataFrame({"budget": budget, "actual": actual})
df["variance"] = df["actual"] - df["budget"]
df["variance_pct"] = df["variance"] / df["budget"]

total_pct = df["variance"].sum() / df["budget"].sum()
average_pct = df["variance_pct"].mean()
print(round(total_pct * 100, 2), round(average_pct * 100, 2))  # 0.88 0.63

The last line shows the error a DAX measure must not make. The ratio of the totals is 0.88%, while the average of the nine row percentages is 0.63%. A Variance % measure built with DIVIDE over the Variance and Budget Amount measures recalculates the ratio at every level and returns 0.88% in the total row; a calculated column averaged in a visual would return the wrong figure.

··········

BUILDING THE VARIANCE VISUALS WITH COPILOT REPORT PAGES.

Once the measures are in the model, open the report, switch to Edit view in the service, select the Copilot icon in the ribbon and either choose Suggest content for this report or type a prompt. Naming the measures keeps Copilot from creating implicit sums of the hidden budget column.

Create a page titled Budget vs Actual Q1 2026. Add a clustered column chart of
Actual Sales and Budget Amount by Date Month Start, a matrix of Actual Sales,
Budget Amount, Variance and Variance % with Category on rows and Month Start on
columns, and a card for Variance %.

Copilot can add, change and delete visuals on the page and supports undo and redo for its own actions. Microsoft documents several limits that affect this dashboard: Copilot does not make styling or formatting changes, does not support custom visuals, and cannot create pages when implicit measures are disabled in the model. Red and green conditional formatting on the Variance column, number formats for percentages and any variance waterfall built with a custom visual stay manual tasks for the report author.

··········

WHERE COPILOT GETS BUDGET VS ACTUAL DAX WRONG.

The most frequent problem in generated budget measures is a plain SUM over the budget column with no grain guard. The query still runs, and the month-by-category result looks correct, so the error appears only when someone adds a date or product field to a visual. Test every budget measure in a query grouped by 'Date'[Date] and by 'Product'[Product]: the correct result is BLANK for the budget, the variance and the variance percentage.

The second problem is the denominator. Variance % for a revenue budget is divided by the budget amount; a query that divides by actual sales returns 5.1% instead of 5.3% for January Hardware in the example. The third is the sign convention, because a positive variance is favorable for revenue and unfavorable for costs, and a single measure cannot serve both without an explicit flag in the model. The fourth is division with the slash operator, which returns infinity when a category has a zero budget; DIVIDE returns BLANK by default.

Microsoft's documentation adds that Copilot may not know recently added DAX functions, uses reduced metadata above documented thresholds such as 500 tables or 5,000 measures, and retries invalid syntax once before returning the query with an error message. A wider view of how Copilot compares across Microsoft tools appears in the report on Microsoft Copilot tools for data analysis.

··········

COPILOT DATA HANDLING AND GOVERNANCE FOR BUDGET REPORTS.

For DAX queries and explanations, Microsoft states that Copilot uses the semantic model metadata, such as table and column names and properties, together with any selected query and the prompt. For report visuals it uses the data in the semantic model combined with the prompt. Microsoft also states that Copilot does not store prompts or responses after the session ends and that data stays within the organization's tenant boundary and is not used to train foundation models.

Tenant administrators can enable or disable Copilot and limit who can access it, and writing DAX queries in the service requires write permission on the semantic model. The Copilot privacy page does not address row-level security, so test a budget report with a restricted test account before publishing it to a wider audience.

··········

POWER BI BUDGET VS ACTUAL WITH COPILOT: WHAT DEPENDS ON CAPACITY AND MODEL DESIGN.

Microsoft's documentation confirms that Copilot can write and explain DAX queries in DAX query view, that query-scoped measures can be added to the model with one command, and that Copilot can build and edit report pages from a prompt. All of it requires a paid Fabric F2 or Premium P1 capacity, tenant and workspace settings turned on, and the right workspace or model permissions; Pro or Premium Per User licenses are not enough on their own.

The modeling decisions remain with the author. The budget table at month and category grain, the one-to-many relationship on the month start, the single-direction many-to-many relationship on category, the ISFILTERED guard and the DIVIDE-based percentage come from Microsoft's modeling guidance and DAX reference, and Copilot follows them only when the prompt or the existing measures spell them out. Checking Copilot's query against a small dataset with known answers, such as the Q1 example with its 6,800 USD total variance and 0.88% variance rate, catches the grain and denominator errors before the dashboard reaches finance or sales leadership.

··········

·····

DATA STUDIOS

·····

datastudios.org