topic Re: Budget, actual and variance matrix in DAX Commands and Tips

Budget, actual and variance matrix

helpme123 — Fri, 17 Apr 2026 08:43:57 GMT

Re: Budget, actual and variance matrix

Natarajan_M — Tue, 14 Apr 2026 12:33:21 GMT

Hi helpme123 , Could you please share some sample values for both the actual and budget data? Since the data comes from two different systems, how are you managing the relationships between them? Additionally, could you provide an image of the data model?

thanks

Re: Budget, actual and variance matrix

A_Veen — Tue, 14 Apr 2026 12:39:37 GMT

Hi helpme123 , Your issue might be caused by the way SELECTEDVALUE works, as it breaks for totals and aggregations when more than one row is in context.

You can try replacing your measure with:

MeasBudget Correct =

SUM('Consolidated for Power BI'[Value]).

If this doesn't work, could you provide more details on how your budget and actual tables are linked

Re: Budget, actual and variance matrix

helpme123 — Fri, 17 Apr 2026 08:44:29 GMT

Thank you for the help it is working now 🙂

Re: Budget, actual and variance matrix

helpme123 — Fri, 17 Apr 2026 08:45:01 GMT

Thank you for the help it is working now 🙂

Re: Budget, actual and variance matrix

cengizhanarslan — Tue, 14 Apr 2026 18:40:33 GMT

The problem is SELECTEDVALUE, it only returns a value when exactly one row exists in filter context, which is why totals return blank and values appear in wrong rows. It is not the right function for a budget measure in a matrix.

Use this instead:

MeasBudget Correct = SUM ( 'Consolidated for Power BI'[Value] )

Re: Budget, actual and variance matrix

dedelman_clng — Tue, 14 Apr 2026 19:05:26 GMT

We have one ACTUALS table which is from a website called twinfield, and this one seems to work using the SUM formula.
We have a BUDGET spreadsheet with codes on each row that match those on twinfield, and dates aong the top also matching those on twinfield. and these two relationships have been built on power bi.

Having relationships between two fact tables is not good practice. You should create dimension tables one will be a single list of the codes, one will be a single list of the dates. If you can't build them in Power Query or thru another method, you can simply use VALUES() or ALL() to create a calculated table.

Then the dimension tables handle the relationships, like this (from my own budget reports)

Your rows and columns then come from the Dim tables, and your data (sum(amount) from the Fact tables.

Hope this helps

David

Re: Budget, actual and variance matrix

v-echaithra — Wed, 15 Apr 2026 06:12:38 GMT

Hi helpme123 ,

The issue might be caused due to model design + date alignment, not the aggregation itself.
The correct approach is to use a proper star schema with Date and Code dimensions, and then rely on a simple aggregation measure.

1. Create a proper Date table
Date = ADDCOLUMNS(
CALENDAR(DATE(2022,1,1), DATE(2026,12,31)),
"YearMonth", FORMAT([Date], "YYYY/MM"))

2. Normalize period columns (Budget & Actuals)

Convert text periods (e.g., "2026/01") into real dates:

PeriodDate = DATE(
LEFT('Consolidated for Power BI'[Attribute],4),
RIGHT('Consolidated for Power BI'[Attribute],2),
1)

Apply the same logic to the Actuals table.

3. Create proper relationships

Use a star schema:

Code Dimension (1) > Budget (Many)
Code Dimension (1) > Actuals (Many)
Date Table (1) > Budget (Many)
Date Table (1) > Actuals (Many)

Cross-filter direction: Single (dimension > fact)

4. Use a simple measure

Budget = SUM('Consolidated for Power BI'[Value])

Hope this helps.
Thank you.

Re: Budget, actual and variance matrix

Natarajan_M — Wed, 15 Apr 2026 14:00:51 GMT

Hi helpme123 , I mocked up the solution using the sample data you provided .

Is this what you are expecting ?

OP:

Data model :

PBIX :
Accounting Sample matrix.pbix

Thanks
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!