Forum Discussion
Calculating with Filters
I am trying to calculate the percentage of work that each employee does on each project. I need the individual hours on each project divided by the total hours from each project. I am getting an error message that there is a calculation error that cannot convert the column with the employee name (LeadDeveloper) to true and false. Im not sure how to fix the dax measure or the columns to properly calculate this.
Consider the example data...
You can get the % of hours by each lead (indicated here as ABC, DEF, etc.) using the context of the visual and the following measure...
%_Hours_Project = DIVIDE( CALCULATE(SUM('Table'[Hours])), CALCULATE(SUM('Table'[Hours]), ALLEXCEPT('Table', 'Table'[ProjectCode])), 0 )To get this result...
2 Replies
Consider the example data...
You can get the % of hours by each lead (indicated here as ABC, DEF, etc.) using the context of the visual and the following measure...
%_Hours_Project = DIVIDE( CALCULATE(SUM('Table'[Hours])), CALCULATE(SUM('Table'[Hours]), ALLEXCEPT('Table', 'Table'[ProjectCode])), 0 )To get this result...
I split it into three simple measures. Instead of filtering manually, I let the visual's row context (EMP + Project) handle the base calculation, then used ALL(P_data[EMP]) to remove just the employee filter so I get the project's total across everyone:
Total_Hrs = SUM(P_data[Actual_Hrs])
Project Hrs = CALCULATE([Total_Hrs], ALL(P_data[EMP]))
%_of_Hours_by_EMP = DIVIDE([Total_Hrs], [Project Hrs], -1)
- Total_Hrs — simple sum, picks up the current EMP + Project from the visual's row context.
- Project Hrs — ALL(P_data[EMP]) clears the employee filter only (keeping Project), so this returns the total hours logged on that project by everyone.
- %_of_Hours_by_EMP — DIVIDE with a -1 fallback, so if a project ever has zero total hours, the measure returns -1 instead of erroring or blanking out (easy to spot as an outlier).
Result:
EMP Project Total_Hrs Project Hrs %_of_Hours_by_EMP
Each employee's share of a project's total hours now adds up to 100% per project as expected.
