Measure for adding column values

[Matthias93]

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

[Anonymous · Accepted 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.