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.
Hi,
I'm currently working on a dashboard where I want to visualize the chargeability of an employee. To do this I'm using an excel table with the following main columns: Project name, Employee name and hours performed. Each row represents one person working on a certain project. I now want to be able to add up all performed hours for an employee over all different projects and divde that by a maxium amount of performable hours to get a percentage of chargeability.
For examle= (hours performed by employee x on project 1 + hours performed by employee x on project 2)+.../maxium performable hours (e.g. 160).
Can anyone come up with a measure that does this?
Thanks in advance,
Matt
Solved! Go to Solution.
A couple of ways to go at it. The simplest would be to mash up everything into one formula. How are maximum performable hours determined? Is 160 a constant or do you want to calculate the actual workable hours in a calendar month? I'm going to assume for now it's a constant. We can come back to it later.
% Chargeability = DIVIDE( CALCULATE( SUM(ProjectTable[HoursPerformed]), FILTER( ProjectTable, ProjectTable[ProjectName] = "Project X" || ProjectTable[ProjectName] = "Project Y" ) ), 160 )
Then I assume you'll do something like graphing that measure on a line chart by month with employee name in the legend. Although you didn't mention a date or month column...
The more flexible method is to make individual measures for each component. Then you can reuse those parts elsewhere. This will take 3 measures. 4 if performable hours is calculated rather than constant.
Hours Project X = CALCULATE( SUM(ProjectTable[HoursPerformed]), FILTER( ProjectTable, ProjectTable[ProjectName] = "Project X" ) ) Hours Project Y = CALCULATE( SUM(ProjectTable[HoursPerformed]), FILTER( ProjectTable, ProjectTable[ProjectName] = "Project Y" ) ) % Chargeability = DIVIDE( ([Hours Project X] + [Hours Project Y]), 160 )
That way you can chart the actual hours too if you want.
Proud to be a Super User!
A couple of ways to go at it. The simplest would be to mash up everything into one formula. How are maximum performable hours determined? Is 160 a constant or do you want to calculate the actual workable hours in a calendar month? I'm going to assume for now it's a constant. We can come back to it later.
% Chargeability = DIVIDE( CALCULATE( SUM(ProjectTable[HoursPerformed]), FILTER( ProjectTable, ProjectTable[ProjectName] = "Project X" || ProjectTable[ProjectName] = "Project Y" ) ), 160 )
Then I assume you'll do something like graphing that measure on a line chart by month with employee name in the legend. Although you didn't mention a date or month column...
The more flexible method is to make individual measures for each component. Then you can reuse those parts elsewhere. This will take 3 measures. 4 if performable hours is calculated rather than constant.
Hours Project X = CALCULATE( SUM(ProjectTable[HoursPerformed]), FILTER( ProjectTable, ProjectTable[ProjectName] = "Project X" ) ) Hours Project Y = CALCULATE( SUM(ProjectTable[HoursPerformed]), FILTER( ProjectTable, ProjectTable[ProjectName] = "Project Y" ) ) % Chargeability = DIVIDE( ([Hours Project X] + [Hours Project Y]), 160 )
That way you can chart the actual hours too if you want.
Proud to be a Super User!
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |