Trying to get lowest value for a person to be in each row of matrix.

[Anonymous]

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.

[amitchandak]

@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.

[Greg_Deckler]

@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

[Anonymous]

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?

[sturlaws]

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