Multiplication measure filtered by hierarchy

[Justas4478]

Hello, I have this measure: 

Basic Pay value = SUM('Agency Data'[Work Hours]) * 10.42
And I need to multiply work hours by pay rate. Problem is that there is different pay rates for different agency.
Is it possible to create measure that would use different pay rate in multiplication based on the agency.
Example Agency 1  - Pay rate £10.42 and if Agency 2 - Pay rate £11.03

Thanks

[amitchandak]

@Justas4478 , Assume your table already have agency name

Create a new table with Agency name and Rate

Join both Table on Agency name

You can create a new column or measure

new column = 'Agency Data'[Work Hours] * related(AgencyRate[Rate])

New measure = sumx( 'Agency Data', 'Agency Data'[Work Hours] * related(Agency Rate[Rate]) )

[Justas4478]

Hi @amitchandak I created slightly more complex table.
I made rates by Shift.My Agency Data table has shift column in it.

Should I join other table by shift instead of agency now?
Does this affect your provided calculated column or measure solutions?

Thanks.