Total is wrong

[Anonymous]

Hi all, 

I have created an measure to calculate the headcount monthly based on data range criterias, for each month the result is correct. However when I display the figures in a table split by month the total is wrong. 

The return from PBI 

Month	HC_month
jan	505
fev	505
mar	498
abr	519
mai	515
jun	516
jul	521
ago	534
set	559
out	555
nov	568
dez	565
TOTAL	5919

The right amount for the total should be 

Month	HC_month
jan	505
fev	505
mar	498
abr	519
mai	515
jun	516
jul	521
ago	534
set	559
out	555
nov	568
dez	565
TOTAL	6360

That's my measure code 

HC_MONTH = COUNTROWS(
                         FILTER(Payroll,
                                 (Payroll[Payroll nb]<>376)&& (Payroll[Payroll nb]<>377)&& (Payroll[Payroll nb]<>445) //exclude 3 specifics employees Expat
                                 &&'Payroll'[Most Recent Hire Date] <= MAX (Calendar[Date])
                                 && (ISBLANK ('Payroll'[Administrative End Date]) || 'Payroll'[Administrative End Date] >= MAX (Calendar[Date]))
                               )
                    )

Any thought that what is wrong on my measure code, or how can I improve it ? 

I have attached an example of the worksheet that is feeding my database on PowerBI. Example

[themistoklis · Accepted solution]

@Anonymous

Change your formula to this one:

HC_MONTH =
SUMX (
    VALUES ( 'Calendar'[Month Name] ),
    CALCULATE (
        COUNTROWS (
            FILTER (
                Payroll,
                ( Payroll[Payroll nb] <> 376 )
                    && ( Payroll[Payroll nb] <> 377 )
                    && ( Payroll[Payroll nb] <> 445 ) //exclude 3 specifics employees Expat
                    && 'Payroll'[Most Recent Hire Date] <= MAX ( Calendar[Date] )
                    && (
                        ISBLANK ( 'Payroll'[Administrative End Date] )
                            || 'Payroll'[Administrative End Date] >= MAX ( Calendar[Date] )
                    )
            )
        )
    )
)

[Anonymous]

@themistoklis 

Thanks a lot for your help.

It's worked properly. 

I didn't think about the SUMX.