Multipli values acrros 2 related tables

[BobKoenen]

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

Date	Employee id	Hours
1-1-2021	1	2
1-1-2021	1	3
1-1-2021	2	5
2-1-2021	2	3

My employee table looks like this

Id	rate
1	10
2	15

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

hope you can help me

[HashamNiaz · Accepted solution]

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