Calculated column returning same value for every row?

[CapnShanty]

So I have three calculated columns that feed into eachother: SubcontractorHours, FTEHours, and % Subcontractor. 

However, when I put % Subcontractor into my matrix that is fed by my other table, Projects, it returns the same value for them all?

What I'm trying to do relies on three tables: 

SubcontractorList (User)

BookedHours (User, Hours, ProjectID)

Projects (ProjectID)

I need to make a calculated column that sums up the subcontractor hours per project. Currently, I'm doing it this way:

BookedHours SubcontractorHours: If user exists in SubcontractorList, sum their hours

BookedHours FTEHours: If user does not exist in SubcontractorList, sum their hours

BookedHours % Subcontractor: SubcontractorHours/(SubcontractorHours+FTEHours)

However, when I drop this into my matrix using data from the Projects table (organized by projectID),  it returns the same value for every row. I'm really out of my depth with DAX here, how do I make a column that does the same thing as % Subcontractor, but it's for every projectID? I think it must have something to do with doing my calculations in the Projects table, but I don't know how to construct it. 

[v-yingjl · Accepted solution]

Hi @CapnShanty ,

I'm not certain whether other rows is 0 or blank or maintain their initial value when you create calculated columns to sum hours.

In my sample, I suppose it to maintain their intial value because if it is 0 or blank the percentage seems meaningless:

SubcontractorHours = 
IF(
    BookedHours[User] in DISTINCT(SubcontractorList[User]),
    SUMX(
        FILTER(
            BookedHours,
            BookedHours[User] in DISTINCT(SubcontractorList[User])
        ),
        [Hours]
    ),
    [Hours]
)

FTEHours = 
IF(
    NOT( BookedHours[User] in DISTINCT(SubcontractorList[User]) ),
    SUMX(
        FILTER(
            BookedHours,
            NOT( BookedHours[User] in DISTINCT(SubcontractorList[User]))
        ),
        [Hours]
    ),
    [Hours]
)

%Subcontractor = [SubcontractorHours] / ([SubcontractorHours] + [FTEHours])

Sample file is attached, please check and try it: Calculated column returning same value for every row.pbix 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

Although there's a lot of info here, it's still an educated guess about what's going on.  Typically, getting the same value for every row in a visual means the relationships are either wrong or missing.