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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
marpisa
Regular Visitor

Exclude some value to Measure

Hi members

 

Sorry, I'm a bit rusty on formulas (I'm just getting back into Powerbi after not using it for months).

 

I have this situation

 

Table  Projects

ProjectNameProject MemberHours
AlphaMarco20
BetaMarco10
GammaAnna10
GammaMarco10

 

 

Table  Timesheet

DayMemberProjectNameWorkedHours
01/01/2021MarcoAlpha8
02/01/2021MarcoAlpha14
01/01/2021MarcoGamma2
02/01/2021MarcoBeta4

 

So I builteda measure that tell me how many hours a Project Member had to work in a single Project

 

 

ProjectMemberHoursWorkedHours (Sum of Timesheet[WorkedHours])
AlphaMarco2022
BetaMarco104
GammaMarco102
GammaAnna100

 

Now I want to know how may hours have to work Marco to complete asigned hours in his project.

The result is 14 (6 hours for project Beta and 8 hours for the project Gamma)

 

To build a measure I can't do a simple SUM Timesheet[WorkedHours] - Projects[Hours] because the result for the Alpha Project is -2

How can I exclude Rows where Timesheet[WorkedHours] > Projects[Hours] ?

 

Thanks

Marco

 

 

 

1 REPLY 1
amitchandak
Super User
Super User

@marpisa , You need common project and memeber dimension and using that you can create measures like

sumX(filter(summarize(Timesheet, Project[ProjectName], Memeber[Project Member], "_1", SUM(Timesheet[WorkedHours]) - Sum(Projects[Hours])),[_1]>0),[_1])

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.