Matrix - Wrong Total

[jabrillo]

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. 

[tamerj1 · Accepted solution]

Hi @jabrillo 

You can try

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

[jabrillo]

This works. Thank you so much.