Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Multiplying Two Columns with Filters in Two Other Columns

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:

EmployeeCost per employee
Javier35
Jorge32
Mary43

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

ID_ProyectoAllocated HoursEmployee
231052Javier
23140Javier
23102Jorge
23148Jorge
2310267Mary
2314458Mary

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.

  • jpessoa8's avatar
    jpessoa8
    2 years ago

    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

5 Replies

  • jpessoa8's avatar
    jpessoa8
    Continued Contributor
    2 years ago

    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

  • jpessoa8's avatar
    jpessoa8
    Continued Contributor
    2 years ago

    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's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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!!

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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!!