Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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] ) ) ) ) ) )