Forum Discussion

Stevo164's avatar
Stevo164
Regular Visitor
8 years ago
Solved

Creating a measure between two differents tables

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?

 

 

 

Need Help
  • _picoloto43's avatar
    _picoloto43
    8 years ago

    Stevo164

     

     

    In table A create this measure! It's works

     

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

5 Replies

  • _picoloto43's avatar
    _picoloto43
    Advocate II
    8 years ago

    Hey Stevo164

     

    I could not understand your question very well...

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

    • Stevo164's avatar
      Stevo164
      Regular Visitor
      8 years ago

      Hi _picoloto43,

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

       

      TABLE A

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

       

      TABLE B

      ContractFTE
      Contract 10,9
      Contract 20,8
      Contract 30,7
      Contract 40,6
      Contract 50,5

       

      The result should be 14,3

      EmployeeContractFTE
      Employee 1Contract 10,9
      Employee 2Contract 20,8
      Employee 3Contract 10,9
      Employee 4Contract 40,6
      Employee 5Contract 20,8
      Employee 6Contract 20,8
      Employee 7Contract 20,8
      Employee 8Contract 10,9
      Employee 9Contract 10,9
      Employee 10Contract 10,9
      Employee 11Contract 10,9
      Employee 12Contract 40,6
      Employee 13Contract 40,6
      Employee 14Contract 40,6
      Employee 15Contract 40,6
      Employee 16Contract 50,5
      Employee 17Contract 20,8
      Employee 18Contract 30,7
      Employee 19Contract 30,7
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Stevo164
      Regular Visitor
      8 years ago

      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's avatar
        _picoloto43
        Advocate II
        8 years ago

        Stevo164

         

         

        In table A create this measure! It's works

         

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