Forum Discussion
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
| ProjectName | Project Member | Hours |
| Alpha | Marco | 20 |
| Beta | Marco | 10 |
| Gamma | Anna | 10 |
| Gamma | Marco | 10 |
Table Timesheet
| Day | Member | ProjectName | WorkedHours |
| 01/01/2021 | Marco | Alpha | 8 |
| 02/01/2021 | Marco | Alpha | 14 |
| 01/01/2021 | Marco | Gamma | 2 |
| 02/01/2021 | Marco | Beta | 4 |
So I builteda measure that tell me how many hours a Project Member had to work in a single Project
| Project | Member | Hours | WorkedHours (Sum of Timesheet[WorkedHours]) |
| Alpha | Marco | 20 | 22 |
| Beta | Marco | 10 | 4 |
| Gamma | Marco | 10 | 2 |
| Gamma | Anna | 10 | 0 |
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
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])
