The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I am looking to create a measure to perfrom the following:
The two tables below are linked by "User Name".
User Name | Project Name | Hours |
Tim | Project 1 | 4 |
Tim | Project 2 | 10 |
Paul | Project 3 | 2 |
Paul | Project 4 | 8 |
Paul | Project 5 | 10 |
User Name | Available Hours |
Tim | 32 |
Paul | 40 |
I need to have a display that calculates as such:
User Name | Used Hours | Remaining Available Hours |
Tim | 14 | 18 |
Paul | 20 | 20 |
Essentially the sum of each persons used hours subtracted from thier available total listed in the other table. Thanks.
Solved! Go to Solution.
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
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
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.
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
7 | |
6 |