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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KLGRIZZARD
New Member

Help creating a measure to subtract the sum of hours from a unique row in another table.

Hello, 

 

I am looking to create a measure to perfrom the following: 

 

The two tables below are linked by "User Name". 

User NameProject NameHours
TimProject 14
TimProject 210
PaulProject 32
PaulProject 48
PaulProject 510

 

User NameAvailable Hours
Tim32
Paul40

 

I need to have a display that calculates as such: 

User NameUsed HoursRemaining Available Hours
Tim1418
Paul2020

 

Essentially the sum of each persons used hours subtracted from thier available total listed in the other table. Thanks. 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @KLGRIZZARD 

Used Hours = SUM ( Table1[Hours] )

 

Remaining Available Hours = 

IF (

    HASONEVALUE ( Table2[Available Hours] ),

    VALUES ( Table2[Available Hours] ) - [Used Hours]

)

 

Then use a matrix visual - Table1or2[User Name] column at the rows and [Remaining Available Hours] measure at the values

 

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @KLGRIZZARD 

Used Hours = SUM ( Table1[Hours] )

 

Remaining Available Hours = 

IF (

    HASONEVALUE ( Table2[Available Hours] ),

    VALUES ( Table2[Available Hours] ) - [Used Hours]

)

 

Then use a matrix visual - Table1or2[User Name] column at the rows and [Remaining Available Hours] measure at the values

 

Samarth_18
Community Champion
Community Champion

Hi @KLGRIZZARD ,

 

Create two measure like below:-

Used Hours = sum(user name[hours])	
Remaining Available Hours = sum(table[available hours]) - sum(user name[hours])

 

Thanks,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Thanks, the first measure I can get to work, the second, not so much. 

 

For example sake the tables are named "Project Hours" and "Available Hours" I am not sure how that relates to the second measure. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors