Sum of visible row values by separate column

[Anonymous]

I'm trying to do a basic sum calculation on a basic table and use it as conditional formatting. I need to sum the hours that each employee works across multiple projects (see table). I am using the below formula, but getting incorrect results - not all employees are highlighted - Both the 35 hours and 10 hour cell should be highlighted for john:

calculate(sum('Project'[Hours]),ALLEXCEPT('Project','Project'[Project_Name],'Project'[Employee]))

Project_Name	Employee	Hours
Lawn	John	35
Factory	John	10
Factory	Mike	40

[V-lianl-msft]

Hi @Anonymous ,

Can you take a screenshot of the relationship between the two tables and the fields in the tables?

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

[az38]

@Anonymous 

try if I understand you correct

calculate(sum('Project'[Hours]),ALLEXCEPT('Project','Project'[Employee]))

[Anonymous]

@az38thank you for the reply

that is the calculation that i've been trying to use, but it does not sum the values. It returns the row by row hours (i.e., 35, 10,40)

[az38]

@Anonymous 

so, whats your desired output based on your data sample?

[Anonymous]

@az38 

Project_Name	Employee	Hours	SUM OF HOURS
Lawn	John	35	45
Factory	John	10	45
Factory	Mike	40	40

[az38]

@Anonymous 

did you try my previous suggest

= calculate(sum('Project'[Hours]), ALLEXCEPT('Project','Project'[Employee]))

[Anonymous]

@az38yes, but it returned 

Project_Name	Employee	Hours	SUM OF HOURS
Lawn	John	35	35
Factory	John	10	10
Factory	Mike	40	40

[az38]

@Anonymous 

I think you are doing something wrong

pay attention I remove

'Project'[Project_Name],

from your statement

[Anonymous]

@az38 - thank you - My table has columns from other database tables as well - do you think that is preventing it from working?

[az38]

@Anonymous 

I think, no, we drop all filters from context besides employee

do you create a measure in the same Project table?

[Anonymous]

the measure is in the project table - but the employee is in a different table