cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors