Percentages of time from different tables

[rmorris]

I've come accross a beauty that i just cannot work out. I've got staff working on jobs and need to show which staff member is generating the most income for us. the data is sitting in2 tables and i need to present it in a Powerbi matrix. 

Table 1

Contract#	Job#	Time SOLD	Sales Value
M309252	1	1.5	$450
M309252	2	2.0	$350
M304445	1	3.0	$400

Table 2

Contract#	Job#	Employee#	Time SPENT
M309252	1	915	0.5
M309252	2	960	0.6
M304445	1	960	0.8
M304445	1	977	1.2

In the PowerBI Matrix i need a "Total Time Spent", "Total Time SOLD" & "Total Sales Value", all to be calclauted based on the % of "Time SPENT" between technicians on each perticlaur contract/job line.

The end result should look like this in a PowerBI Matrix. I'm sure i'll need some form of measure but have no idea how to do it.

Employee#	Total Time SPENT	Total Time SOLD	Total Sales Value
915	0.5	1.5	$450
960	1.4	3.2	$510
977	1.2	1.8	$240

[tamerj1]

Hi @rmorris 

create a many to many relationship between the two tables on Contract# then use the following measures

Total Time SPENT =
SUM ( Table2[Time SPET] )

Total Time SOLD =
SUMX (
Table2,
SUMX (
FILTER ( RELATEDTABLE ( Table1 ), Table1[Job#] = Table3[Job#] ),
Table1[Time SOLD]
)
)

Total Sales Value =
SUMX (
Table2,
SUMX (
FILTER ( RELATEDTABLE ( Table1 ), Table1[Job#] = Table3[Job#] ),
Table1[Sales Value]
)
)

[BrianConnelly]

In both tables, you need to create a composite key (calculated column: M309252-1) and create a relationship.  Then create a summary table.

SummaryTable =

SUMMARIZECOLUMNS('Table2'[Employee],"Total Time Spent", SUM('Table2'[TimeSpent]),"Total Time SOLD",SUM('Table1'[TimeSold]) ,"Total Sales Value",SUM('Table1'[SalesValue])

You can then create measures on top of this table.