Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
@Anonymous
try if I understand you correct
calculate(sum('Project'[Hours]),ALLEXCEPT('Project','Project'[Employee]))
@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)
@Anonymous
so, whats your desired output based on your data sample?
Project_Name | Employee | Hours | SUM OF HOURS |
Lawn | John | 35 | 45 |
Factory | John | 10 | 45 |
Factory | Mike | 40 | 40 |
@Anonymous
did you try my previous suggest
= calculate(sum('Project'[Hours]), ALLEXCEPT('Project','Project'[Employee]))
@az38yes, but it returned
Project_Name | Employee | Hours | SUM OF HOURS |
Lawn | John | 35 | 35 |
Factory | John | 10 | 10 |
Factory | Mike | 40 | 40 |
@Anonymous
I think you are doing something wrong
pay attention I remove
'Project'[Project_Name],
from your statement
@az38 - thank you - My table has columns from other database tables as well - do you think that is preventing it from working?
@Anonymous
I think, no, we drop all filters from context besides employee
do you create a measure in the same Project table?
the measure is in the project table - but the employee is in a different table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |