Creating a measure between two differents tables

[Stevo164]

Hi everyone,

I have two tables A and B.

A contains all the names and the contracts of all the employees. B contains all the possible types of contracts and the FTE referred to each contract.

The two tables are in realtion by the contracts column.

How can I obtain the total FTE of the entire company?

[Anonymous]

@Stevo164,

You can create measure like this:

Total Company FTE = CALCULATE(SUM(B[FTE]), ALL(B))

This will display the total FTE ignoring any filters you have on your report page. If you want total FTE that filters by employee name, or contract type, use this: 

Total FTE = SUM(B[FTE])

[Stevo164]

thank you @Anonymous,

but that doesn't work for me. It will only shows the total FTE per type of contract with no relation with the number of employees.

[_picoloto43 · Accepted solution]

@Stevo164

In table A create this measure! It's works

FTE = SUMX(Table A; RELATED(Table B[FTE]))

[_picoloto43]

Hey @Stevo164

I could not understand your question very well...

I think you want make group by types of contracts, right?

[Stevo164]

Hi @_picoloto43,

sorry for my bad english. Let give you and example:

TABLE A

Employee	Contract
Employee 1	Contract 1
Employee 2	Contract 2
Employee 3	Contract 1
Employee 4	Contract 4
Employee 5	Contract 2
Employee 6	Contract 2
Employee 7	Contract 2
Employee 8	Contract 1
Employee 9	Contract 1
Employee 10	Contract 1
Employee 11	Contract 1
Employee 12	Contract 4
Employee 13	Contract 4
Employee 14	Contract 4
Employee 15	Contract 4
Employee 16	Contract 5
Employee 17	Contract 2
Employee 18	Contract 3
Employee 19	Contract 3

TABLE B

Contract	FTE
Contract 1	0,9
Contract 2	0,8
Contract 3	0,7
Contract 4	0,6
Contract 5	0,5

The result should be 14,3

Employee	Contract	FTE
Employee 1	Contract 1	0,9
Employee 2	Contract 2	0,8
Employee 3	Contract 1	0,9
Employee 4	Contract 4	0,6
Employee 5	Contract 2	0,8
Employee 6	Contract 2	0,8
Employee 7	Contract 2	0,8
Employee 8	Contract 1	0,9
Employee 9	Contract 1	0,9
Employee 10	Contract 1	0,9
Employee 11	Contract 1	0,9
Employee 12	Contract 4	0,6
Employee 13	Contract 4	0,6
Employee 14	Contract 4	0,6
Employee 15	Contract 4	0,6
Employee 16	Contract 5	0,5
Employee 17	Contract 2	0,8
Employee 18	Contract 3	0,7
Employee 19	Contract 3	0,7