Need Help- Relationships and formulas

[pipechaves]

Hi members,

I need some advice. As today I got a really strange issues from a fellow.

He got 3 tables.

One contains the Project Name, Total Hours and other fields. I will just mention the important ones.
Another one is the Rate and PMO Location
and the last one will be the Project: Which contains PMO Location, Project Name and others.

What he is trying and I couldnt succed was to create a new column ON PROJECT that its formula will be Rate*Hours

Rate is on the Rate table and Hours in the Time table.

I wasnt able to do so.

Any help will be appreciated.

Table data below

Table: Time

Hours	# of PMOResources	Period	Project
300	5	September 2017	Apollo
42	1	October 2017	Apollo
3	1	November 2017	Apollo
28	1	December 2017	Apollo
33	1	January 2018	Apollo
91	3	February 2018	Apollo
68	3	March 2018	Apollo
64	2	April 2018	Apollo
22	1	May 2018	Apollo
72	3	January 2018	Aries
54	2	February 2018	Aries
63	2	March 2018	Aries
61	2	April 2018	Aries
38	1	May 2018	Aries
53	2	February 2018	Gemini
29	1	March 2018	Gemini
15	1	April 2018	Gemini
100	4	May 2018	Gemini
200	3	August 2017	Leo
50	1	September 2017	Leo
83	2	October 2017	Leo
19	1	November 2017	Leo
57	2	December 2017	Leo
85	1	August 2017	Orion
75	1	September 2017	Orion
19	1	April 2018	Pegasus
60	3	May 2018	Pegasus
48	2	May 2018	Taurus
100	2	June 2017	Titan

Table: Rate

PMO Location	Rate
APAC	80
EMEA	90
LATAM	100
NA	110

Table: Project

Project Name	Project Manager	Project Start Date	Project Finish Date	PMO Location
Apollo	Bob	9/13/2017		APAC
Aries	Bob	1/4/2018		EMEA
Gemini	Tim	2/18/2018		EMEA
Leo	Sally	8/28/2017	12/30/2017	LATAM
Orion	Tina	8/15/2017	9/30/2017	LATAM
Pegasus	Sally	4/4/2018		NA
Taurus	Angie	5/1/2018		NA
Titan	Tina	6/1/2017	6/30/2017	APAC

It seems the relationships created where good and so on. Any help/explanation will be of great help.

Thank you all

[Vvelarde · Accepted solution]

@pipechaves

Hi, Friend, try with this calculated column

Rate*Hours =
CALCULATE ( SUM ( 'Time'[Hours] )
    * RELATED ( Rate[Rate] )

[pipechaves]

it seems that might work.

Do you mind explaining why the use of related and so on insted of doing it directly ?

Thank you

[Vvelarde]

@pipechaves

The RELATED function returns to you a value from another related table.

So in this case you want the Rate from RateTable (From One to Many) and the function search (or Look) the Row with the coincidence according to the Key of the relationship (PMO Location)