Forum Discussion

grdi's avatar
grdi
Frequent Visitor
1 year ago
Solved

SUMIFS function for unrelated tables using CONTAINS

I have 2 tables that are unrelated.  Essentially an employee master table, a table that essentially logs activities and assigns Yes No values.  If multiple employees perform the activity, for whateve...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi grdi 

     

    Try this:

    Sum of Yes = 
CALCULATE (
    SUM ( activityTable[Yes] ),
    KEEPFILTERS (
        CONTAINSSTRING ( activityTable[Activity], SELECTEDVALUE ( empTable[empID] ) )
    )
)

     

    Change the column name for the sum of no.

     

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi grdi 

 

Try this:

Sum of Yes = 
CALCULATE (
    SUM ( activityTable[Yes] ),
    KEEPFILTERS (
        CONTAINSSTRING ( activityTable[Activity], SELECTEDVALUE ( empTable[empID] ) )
    )
)

 

Change the column name for the sum of no.

 

grdi's avatar
grdi
Frequent Visitor
1 year ago

Thanks for the reply danextian .  I'm using your formula but not getting the correct results.  For some reason I'm only getting a count of 1 for one employee.  And I know there should be about 15 employees with a combined total of 60+ for this calculation.  Also odd that the name shows one but the total is 208.  I think those are the ones with no match?  Which is possible as well as this is supposed to only be for a specific subset of employees.  Here is the code I'm using and the result I'm getting.

 

 

EDITING HERE:  I beleive an existing relationship with another column is what was causing my formula not to work as the solution.  I uploaded a duplicate table with the additional columns that formed the relationship removed, and it worked as intended.  So solution is good and will mark it as such.  Wanted to include the edit for future reference for others searching.