cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Matrix - Wrong Total

I want to create this matrix:

 Q1 Q2 Q3 Q4 Total Planned 10 10 10 10 40 Actual 5 7 12 Estimate by EOY 5 7 10 10 32

Data

Planned Table

 Project Quarter Resource PlannedAmount P1 Q1 A 5 P1 Q1 B 5 P1 Q2 A 5 P1 Q2 B 5 P1 Q3 A 5 P1 Q3 B 5 P1 Q4 A 5 P1 Q4 B 5

Actual Table

 Project Quarter Resource ActualAmount P1 Q1 A 3 P1 Q1 B 2 P1 Q2 A 3 P1 Q2 B 4

Matrix

Column = Quarter

Rows:

Planned = sum(PlannedAmount)

Actual = sum(ActualAmout)

Estimate by EOY = if([Actual] > 0, [Actual], [Planned])

My Total Estimate by EOY is showing 12, instead of 32. Any suggestions would be appreciated. Thank you.

1 ACCEPTED SOLUTION
Super User

You can try

``````Estimate by EOY =
SUMX (
VALUES ( TableName[Quarter] ),
CALCULATE ( IF ( [Actual] > 0, [Actual], [Planned] ) )
)``````

2 REPLIES 2
Super User

You can try

``````Estimate by EOY =
SUMX (
VALUES ( TableName[Quarter] ),
CALCULATE ( IF ( [Actual] > 0, [Actual], [Planned] ) )
)``````

Helper I

This works. Thank you so much.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors