Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So, I track PTO in Azure DevOps on a card within a certain project. I calculate individual capacity based on [number of hours in the sprint]-[PTO hours planned]. I have a measure that rolls up the correct number to the top level of the matrix, but when I drill down on an individual person, it shows the number of hours in the sprint on every row except the project where PTO is tracked.
My measure:
IndividualCapacity = COALESCE ( SUMX ( FILTER ( 'All Sprints', 'All Sprints'[Area.AreaPath] = "Project 2" && 'All Sprints'[PTOIndex] = 1 ), [CapacityHours] - [PTOHoursRemaining] ), [CapacityHours] )
[PTOIndex] is simply a 1 if first three characters of the task title is "PTO" which is only used on these cards. What I'm getting in my matrix (bold is top level, italics is sublevel) is
Name/Project | Remaining Work | Individual Capacity | Commitment Load |
Name | 38.00 | 40 | 95.00% |
Project1 | 22.00 | 80 | 27.5% |
Project2 | 16 | 40 | 40.00% |
So, I'd like the value for Project 2 (our internal project where a person might have work, but also where PTO is tracked) Individual Capacity to override everything else and show up as the same value in each Project for that person. I hope this makes sense. I can try to clarify.
So, in the sample table above, the person is planning to take a week off, which is tracked in Project 2 and calculates his individual capacity at 40 hours. This works for all the other visuals, but what I want to do is to have that 40 hours apply to all the other rows for that person in the matrix. So in Project 1 it would also show 40 hours.
@Anonymous ,
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format?
Appreciate your Kudos.
@Anonymous - I'm not quite getting this one. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Almost seems like the reverse of a measures total problem?
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Hi, thanks so much for replying. I am sorry it wasn't clear enough. I was trying to remove some PII and I think that made it more vague. I have added a bit at the bottom that hopefully clarifies?
Hi @Anonymous
can you give this a go:
IndividualCapacity =
COALESCE (
SUMX (
FILTER (
allexcept('All Sprints',[employee]),
'All Sprints'[Area.AreaPath] = "Project 2"
&& 'All Sprints'[PTOIndex] = 1
),
[CapacityHours] - [PTOHoursRemaining]
),
[CapacityHours]
)
where [employee] the column identifying the employees.
Cheers,
Sturla
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |