Forum Discussion

CapnShanty's avatar
CapnShanty
Frequent Visitor
6 years ago
Solved

Calculated column returning same value for every row?

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. 

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    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.

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    6 years ago

    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.

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    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.