Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Total is wrong

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 m...
Need Help
  • themistoklis's avatar
    themistoklis
    7 years ago

    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] )
                    )
            )
        )
    )
)
themistoklis's avatar
themistoklis
Community Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

themistoklis 

 

Thanks a lot for your help.

 

It's worked properly. 

 

I didn't think about the SUMX.