Unique fields and sum values

[Anonymous]

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?

[v-jingzhang · Accepted solution]

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.

[Nathaniel_C]

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]

Hi @Nathaniel_C 

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

[Nathaniel_C]

Hi @Anonymous , maybe you could show expected outcome.

[Nathaniel_C]

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]

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.

[Anonymous]

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]

Hi,

This measure works

FTE hours = min(Data[FTE])

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

Hope this helps.