Multiplying Two Columns with Filters in Two Other Columns

[Syndicate_Admin]

Hello!

I'm an initiate on this subject and I'm pretty lost. Let's see if anyone can lend me a hand.

I have 2 boards:

1. Hourly Cost Per Employee Table:

Employee	Cost per employee
Javier	35
Jorge	32
Mary	43

2. Table. Employee Hours per Project: Each employee allocates their hours to different Projects (ID_Proyecto).

ID_Proyecto	Allocated Hours	Employee
2310	52	Javier
2314	0	Javier
2310	2	Jorge
2314	8	Jorge
2310	267	Mary
2314	458	Mary

I need to calculate a measure that is the labor cost per project. In the case of Project 2310 it would be equal to 52*35 + 2*32 + 267*43.

I have many more Projects and many more employees, so the solution would have to be generic (not particularized for Jorge, Javier or María).

Any ideas on where to start?

Thank you very much in advance.

[Syndicate_Admin]

Thank you so much @jpessoa8 !!

Now it works perfectly.

Best regards.

[jpessoa8]

Hi @Syndicate_Admin,

I've noticed that issue (I was doing the SUMX only on the ID_Proyecto level and didn't realize there could be multiple Employees in the same project) and I did an edit on my post and I don't know why it didn't reflect the changes in the Spanish Version of the community post.

So, what you need to change is the table input in the first parameter of the SUMX:

SUMX(
	'Projects',
	[AllocatedHours] * [EmployeeCost]
)

With this you will get the correct values

Here you can find the PowerBI file with the correct version : https://we.tl/t-FVD7xxpQXg

For additional help, please @ me in your reply!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa

[Syndicate_Admin]

Hello @jpessoa8 !! How is it going?

I've been reviewing your solution and I think there's some error in the calculation and I don't really know how to solve it. I'll tell you.

• Project 2310 labor cost: In your solution, the calculation comes out to €35,310. However, it should be the sum of Jack's labor cost (52 * 35 = 1820) + George's labor cost (2 * 32 = 64) + Mary's cost (267 * 43 = 11481). The total of this sum is €13,365 vs. €35,310.
• Project 2314 labor cost: In Excel it costs me €19,950 (8*32 + 458 *43) and in power bi it costs €51,260.
• Similarly, the "TotalCost" column in your power bi table adds up to 86,570 when it should be 33,315.

I get the feeling that when you filter through project 2310 or 2314 you're multiplying and adding more terms than you should.

Thanks in advance!!

[Syndicate_Admin]

Wow @jpessoa8 !! Thanks a lot!!

I've been looking at the file and it looks really good.

I'm going to test your solution in my database, which is much larger, and if I have any problems, I'll post again.

Really, thank you so much!!

[jpessoa8 · Accepted solution]

Hello @Syndicate_Admin ,

The first set to do this Calculation is to have both tabels with a relationship like the following:

With this you can create 3 measures to get your result (or even get everything with only one):

First create a measure that returns the total cost per employee

EmployeeCost = SUM(Employee[Cost per employee])

Another measure for the Allocated Hours

AllocatedHours = SUM(Projects[Allocated Hours])

Now, want you want is that, for every project, to do the product of both this measures and afterwards sum all of this produtcs. For this, in DAX, you need to use a iterator function like SUMX which has as first parameter a table to iterator from, and as second parameter a calculation to be applyed to each row of that table. In your case this should be the expression:

SUMX(
	'Projects',
	[AllocatedHours] * [EmployeeCost]
)

This is the final outcome for the dataset provided 

Here you can find a PowerBI file with this exercise : https://we.tl/t-FVD7xxpQXg

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa