Forum Discussion
How to count all duplicate and single values
Hi,
I would like to count the number of employee by department, and I use this formula
Attrition HC 2 = CALCULATE( COUNTROWS(except(values(Terminate[ID]),VALUES(Zero[Employee ID]))),Reasons_LookUp[Involuntary/Voluntary]="Voluntary")
Following is the example of the table Terminate[ID]
| Employee ID | Department | Status |
| 12345 | Finance | Voluntary |
| 12345 | Finance | Voluntary |
| 23456 | Account | Voluntary |
| 34567 | Human Resources | Voluntary |
| 45678 | Sales | InVoluntary |
Following is the example of the table Zero[Employee ID]
| Employee ID | Department |
| 99999 | Marketing |
| 88888 | Marketing |
However, the result i got is
Which it should actually be 2 in finance and the total should be 4.
Please help me 🙏
Thank you
I see so you want to exclude Zero values.
With that in mind try this:Measure = CALCULATE(COUNT('Table (2)'[Employee ID]),'Table (2)'[Status]="Voluntary",EXCEPT(VALUES('Table (2)'[Employee ID]),VALUES('Zero'[EID])))
Test: (no except)With:
4 Replies
- ValtteriN
Community Champion
Hi,
This should do what you want:Measure = CALCULATE(COUNT('Table (2)'[Employee ID]),'Table (2)'[Status]="Voluntary")
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/- ValtteriN
I see so you want to exclude Zero values.
With that in mind try this:Measure = CALCULATE(COUNT('Table (2)'[Employee ID]),'Table (2)'[Status]="Voluntary",EXCEPT(VALUES('Table (2)'[Employee ID]),VALUES('Zero'[EID])))
Test: (no except)With: