Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jabrillo
Helper I
Helper I

Matrix - Wrong Total

I want to create this matrix:

 Q1Q2Q3Q4Total
Planned1010101040
Actual57  12
Estimate by EOY57101032

 

Data

 

Planned Table

ProjectQuarterResourcePlannedAmount
P1Q1A5
P1Q1B5
P1Q2A5
P1Q2B5
P1Q3A5
P1Q3B5
P1Q4A5
P1Q4B5

 

Actual Table

ProjectQuarterResourceActualAmount
P1Q1A3
P1Q1B2
P1Q2A3
P1Q2B4

 

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
tamerj1
Super User
Super User

Hi @jabrillo 

You can try

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

 

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @jabrillo 

You can try

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

 

This works. Thank you so much.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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