cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BobKoenen
Helper III
Helper III

Multipli values acrros 2 related tables

Hi 

 

I have a large trfact table with the hours per employee per date. I also have an employee table which is linked to this tabel which contains the Hourly rate. I want to Calculate the [sum of hours] * [hourly rate] without having to add an extra column to my large fact table.

Hours table

DateEmployee idHours
1-1-202112
1-1-202113
1-1-202125
2-1-202123

 

My employee table looks like this

Idrate
110
215

 

I Want to multiply the hourly rate times the sum of the hours to get the total amount. 

hope you can help me

1 ACCEPTED SOLUTION
HashamNiaz
Solution Sage
Solution Sage

Hi @BobKoenen !

You need to have following 2 measure;

 

 

Total Hours = SUM(Hours[Hours])

Total Amount = SUMX(RELATEDTABLE(Employee), [Rate] * [Total Hours])

 

Your Employee & Hours table needs to be connected through Active relationship based on EmployeeID column.

 

The first measure calculates [Total Hours], second measure calculates [Total Amount], You can plot this against EmployeeID or any other attribute.

 

Regards,

Hasham

View solution in original post

1 REPLY 1
HashamNiaz
Solution Sage
Solution Sage

Hi @BobKoenen !

You need to have following 2 measure;

 

 

Total Hours = SUM(Hours[Hours])

Total Amount = SUMX(RELATEDTABLE(Employee), [Rate] * [Total Hours])

 

Your Employee & Hours table needs to be connected through Active relationship based on EmployeeID column.

 

The first measure calculates [Total Hours], second measure calculates [Total Amount], You can plot this against EmployeeID or any other attribute.

 

Regards,

Hasham

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors