Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Unique fields and sum values

Hi,

I have a table where employee numbers are duplicated due to the report to field.

If someone is Acting in a position, the record duplicate. 
I need two things

One, count unique employees ( I have done this via merging some columns, happy for a better way)

The one I'm having trouble with is trying to count the actual FTE (Full Time Equivalent) which is a numeric value based on uniue employees.

 

Can anyone assist who I can do this?

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    If you are happy with a calculated table, you can create a new table with below DAX codes to get all FTE values without duplicates.

    Sum Table = 
SUMMARIZE (
    'Test Data',
    'Test Data'[Employee Name],
    'Test Data'[Employee Number],
    'Test Data'[Position Number],
    "FTE value", MIN ( 'Test Data'[FTE] )
)

     

    Then use the data from above new table to calculate the actual FTEs and count unique employees with measures.

    Number of Employees = DISTINCTCOUNT('Sum Table'[Employee Name])
    Total FTE = SUM('Sum Table'[FTE value])

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

8 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    5 years ago

    Hi Anonymous , could you mock up a table in Excel to show us? If someone is Acting in a position, the record duplicate, so for instance I don't understand your previous sentence.  

    Is there no EID field that you could use DISTINCTCOUNT() on?

    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Nathaniel_C 

      Please see test data below

       

      Test Data 

       

      So the total FTE sum for John DO should be 

      0.421052632 + 0.197368421 = 0.618421053

       

      But at the moment, I'm getting 

      0.421052632 + 0.197368421 + 0.197368421 = 0.815789474

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        This measure works

        FTE hours = min(Data[FTE])
        Measure = sumx(VALUES(Data[Position Number]),[FTE hours])

        Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Also WAMI is unique for the individual
      They can have multiple positions, which gives them a different Employee Number
      The Issue is that when someone is temporarily relieving someone on leave and is a manager, the employee shows that he has two managers and thus duplicates in the table, but I need only to count it once.

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    5 years ago

    Hi Anonymous , above is your table, sorry slightly different set. Go to Transform Data.

    Highlight the EID col, remove rows, remove duplicates.

     

    You will end up with this, and then you can sum the FTEs

    Sorry, this was a bit rushed, I am out the door, but will check back later.


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Nathaniel_C 

      This will remove some employee numbers that need to stay there.

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        5 years ago

        Hi Anonymous , maybe you could show expected outcome.

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi Anonymous 

     

    If you are happy with a calculated table, you can create a new table with below DAX codes to get all FTE values without duplicates.

    Sum Table = 
SUMMARIZE (
    'Test Data',
    'Test Data'[Employee Name],
    'Test Data'[Employee Number],
    'Test Data'[Position Number],
    "FTE value", MIN ( 'Test Data'[FTE] )
)

     

    Then use the data from above new table to calculate the actual FTEs and count unique employees with measures.

    Number of Employees = DISTINCTCOUNT('Sum Table'[Employee Name])
    Total FTE = SUM('Sum Table'[FTE value])

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.