Count number of employees whos charges are greater than 0 by month

[PowerPaul]

I am on a project where employees charge to the said project for their work. Some of the charges are Negative #s due to corrections from the previous month or other issues. Each employee can have multiple charges per month, both positive (above zero) and negative (less than zero). 

I would like to count how many employees total charges are greater than zero per month. Each employee being counted only once per month.

I came up with this, however its counting the # of employee's with charges greater than zero even if the total for the month is less than zero. So if an employee has 1 positive charge and 2 negative charges equaling -$5,000 its counting the positive when it shouldnt because the total is less than zero. 

Act#Emp:=CALCULATE(DISTINCTCOUNT('FY22-23_LaborActuals_FTE_CTR'[Employee Name]),'FY22-23_LaborActuals_FTE_CTR'[GBE - Actual $]>0) 

The Labor actuals table is linked to the employee table and Date table. 

Labor actuals consists of the following:

Fiscal Year / Period, Employee ID, Employee Name, Actual $ (Any charge both positve and negative)

4/1/2021                    123456           John Doe           $1,000

4/1/2021                    123456           John Doe           $1,000

4/1/2021                    123456           John Doe           $1,000

5/1/2021                    123456           John Doe          -$5,000

5/1/2021                    123456           John Doe           $1,000

5/1/2021                    123456           John Doe           $1,000

5/1/2021                    123456           John Doe           $1,000      

6/1/2021                    123456           John Doe           $1,000

(all the way until present with each period starting with the first of the month)

In summary: How many employees have a total charge greater than zero for each month? Each employee being counted only once.

[PowerPaul · Accepted solution]

Tom, I figured it out! 

Thank you for the reply and the solution. This is great!

Here is how I fixed it.

TomsEmployeeMeasure =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Employee ID] ),

'Table Name'[Employee ID]<>10000001,

'Table Name'[Employee ID]<>10000002,
FILTER (
VALUES ( 'Table'[Employee ID] ),
CALCULATE ( SUM ( 'Table'[Actual $] ) ) > 0
)
)

[PowerPaul]

Tom, thank you for the reply. It works, however I forgot to mention that I need to exclude 2 employee ids because they are not actually people but rather "other" charges. 

 I tried the following as calculate filters inside your formula but keep getting errors. 

TomsEmployeeMeasure =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Employee ID] ),
FILTER (
VALUES ( 'Table'[Employee ID] ),
CALCULATE ( SUM ( 'Table'[Actual $] ) ) > 0,'Table Name'[Employee ID]<>10000001,'Table Name'[Employee ID]<>10000002)
)
)

[tackytechtom]

Hi @PowerPaul ,

Here might be a solution:

And here the measure:

TomsEmployeeMeasure = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Employee ID] ),
    FILTER (
        VALUES ( 'Table'[Employee ID] ),
        CALCULATE ( SUM ( 'Table'[Actual $] ) ) > 0
    )
)

Let me know, if this solved your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/