Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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] ) ) ) ) )
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
It worked properly.
Thanks a lot.
It was getting me nuts, i could not understand why the sum was not correct...
@Anonymous Great to see it workes, that's exactly what my first reply. Cheers
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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] ) ) ) ) )
@Anonymous there are many posts related to this, here is one, hope it is helpful
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks for reply, but it won't help me as I have some conditionsin the formula and it's not a simple SUM.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |