Budget, actual and variance matrix

[cengizhanarslan]

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] )

[A_Veen]

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

[helpme123]

Thank you for the help it is working now 🙂 

[v-echaithra]

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.

[dedelman_clng]

---

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

[Natarajan_M]

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 

[helpme123]

Thank you for the help it is working now 🙂 

[Natarajan_M · Accepted solution]

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!