cancel
Showing results for
Did you mean:
Helper IV

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

 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

1 ACCEPTED SOLUTION
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

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

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors