Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi, Friend, try with this calculated column
Rate*Hours = CALCULATE ( SUM ( 'Time'[Hours] ) * RELATED ( Rate[Rate] )
Hi, Friend, try with this calculated column
Rate*Hours = CALCULATE ( SUM ( 'Time'[Hours] ) * RELATED ( Rate[Rate] )
it seems that might work.
Do you mind explaining why the use of related and so on insted of doing it directly ?
Thank you
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)
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |