Wrong SUM for total

[Anonymous]

Hi I am using the following code for a measure to calculate the Full time equivalence. 

FTE = SUMX (
    VALUES ( 'Calendar'[Month] ),
    CALCULATE (
         SUM(Payroll[FTE])/100,
             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] )
                    )
            )
        )
    )

When i show figures split by month in a table, the figure for each month is correct, but the total is wrong. 

Month	Headcount	FTE
jan	505	499.29
fev	505	498.46
mar	498	491.46
abr	519	512.32
mai	515	508.27
jun	516	508.51
jul	521	513.51
ago	534	526.51
set	559	551.07
out	555	547.07
nov	568	560.07
dez	565	557.55
TOTAL	6360	70049.71

What id like to have as TOTAL for FTE is 6274.09 

Any thoughts what is going worng or any thoughts about amends that ican do to make it work properly? 

Example FTE worksheet

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Based on my test, to create two measures separately should work well.

sum_nEW = CALCULATE (
        SUM('Payroll'[FTE])/100,
             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] )
                    )))

FTE _NEW = SUMX(VALUES('Calendar'[Month]),[sum_nEW])

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Based on my test, to create two measures separately should work well.

sum_nEW = CALCULATE (
        SUM('Payroll'[FTE])/100,
             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] )
                    )))

FTE _NEW = SUMX(VALUES('Calendar'[Month]),[sum_nEW])

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

It worked properly. 

Thanks a lot. 

It was getting me nuts, i could not understand why the sum was not correct... 

[parry2k]

@Anonymous Great to see it workes, that's exactly what my first reply. Cheers

[themistoklis · Accepted solution]

@Anonymous

Use another calculate after the calculate function that you aleardy have and this will do the trick:

FTE = SUMX (
    VALUES ( 'Calendar'[Month] ),
    CALCULATE (
         CALCULATE(SUM(Payroll[FTE]))/100,
             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] )
                    )
            )
        )
    )

[parry2k]

@Anonymous there are many posts related to this, here is one, hope it is helpful

[Anonymous]

@parry2k 

Thanks for reply, but it won't help me as I have some conditionsin the formula and it's not a simple SUM. 

[parry2k]

@Anonymous it would, try this, create a seperate measure for SUM and then another measure with SUMX and using existing created sum measure in your sumx. It is all about the filter context. I would do it for you but bit busy with something else right now.

[parry2k]

@Anonymous based on your data i just did what i explained earlier and this is the result i'm getting

S = sum with filter you provided in original post

X = SUMX using S

[Anonymous]

@parry2k

Thanks for your help, but the result you get it's wrong, you're getting as result for FTE in JAN 3.11 but the right figure for JAN is 499.29