Measure combining two tables on same column value

[TeunVerhagen11]

Hi all,

Im trying to calculate the efficiency % with the use of two tables.

The model looks like this:

So the data that is necessary to calculate the efficiency is connected to a fact table containing all employee data. The relationship uses the employee code. This relationship is necessary for other purposes withing the dataset so it cannot be changed.

The activity and logging table look as follows:

Table 1 contains all executed activities and a predetermined time in within the activity should be executed. (Standard time). Next to that, the activities are classified in working categories. (Category).

Table 2 contains the time logged by employees. They log time by selecting a working category.

So in order to calculate the efficiency I started with the formula: Standard Time Earned = SUM(Standard Time). The calculation for efficiency should be: Standard Time Earned / Time Logged (see example below).

The problem that I face is that no connection between the working categories exist (because of the employee relationship). I think the solution lies in creating this connection within the measure but I have no idea how to do this.

Could someone help me with this? If more information is needed I can provide it.

Many thanks in advance.

Teun

[TeunVerhagen11]

Wow yeah it really is that easy. Thanks very much.

[PaulDBrown · Accepted solution]

Create a new dimension table for Category (as you have done with employee)