Forum Discussion
Solved
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 erro...
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.