Log In
Register
Home
August 26, 2019
Offline
Hello,
I am new in This group, thanks in advance. Any one please help me to how to count Average head count. That can help me lot.
| Heading | Discretion | FX Formula |
| JoinersCounts | Per month Joiner | CALCULATE(COUNTROWS(Joiners)) |
| leaversCounts | Per month Leavers | CALCULATE(COUNTROWS(Leavers)) |
| Active Head Count | Net Change | CALCULATE([JoinersCounts]-[leaversCounts], FILTER(ALLSELECTED ('Date'[Date]), ISONORAFTER ('Date'[Date], MAX('Date'[Date]), DESC))) |
| YTD_Joiners | Year star date till month JoinersCounts | CALCULATE([JoinersCounts], DATESYTD('Date'[Date],"31/12")) |
| YTD_Leavers | Year star date till month leaversCounts | CALCULATE([leaversCounts], DATESYTD('Date'[Date],"31/12")) |
| YTD Avg HC | Avg of Year till of Active Active Head Count | Can you please how to count Average of Active Head count |
| Month | numric Month No | No of month / Quarter |
| Monthly Attrition | (YTD_Leavers/YTD Avg HC*12)/Month | Can you please how to count Monthly Attrition |
| Date (Quarter) | Date (Month) | Joiners Counts | leavers Counts | Active Head Count | YTD Joiners | YTD Leavers | Avg HC | Month | Monthly Attrition |
| Qtr1 | Jan | 10 | 5 | 25 | 10 | 5 | 25 | 1 | 240.0% |
| Feb | 15 | 7 | 33 | 25 | 12 | 29 | 2 | 248.3% | |
| Mar | 20 | 8 | 45 | 45 | 20 | 34 | 3 | 233.0% | |
| Qtr1 Total | 45 | 20 | 45 | 45 | 20 | 34 | 3 | 235.3% | |
| Qtr2 | Apr | 15 | 4 | 56 | 60 | 24 | 51 | 4 | 142.6% |
| May | 20 | 5 | 71 | 80 | 29 | 57 | 5 | 121.4% | |
| Jun | 25 | 9 | 87 | 105 | 38 | 65 | 6 | 117.4% |
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Hitesh,
RunningSum = SUMX(FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])),[Active Head Count])
A month Count is needed:
MonthCount =
VAR _start = STARTOFYEAR( 'Calendar'[Date] ) -1
VAR _end = ENDOFMONTH( 'Calendar'[Date] )
RETURN DATEDIFF( _start, _end, MONTH )
(you can use though the Month column you have)
YTD Avg HC=DIVIDE(RunningSum, MonthCount)
Monthly Attrition = ([YTD_Leavers]/[YTD Avg HC]*12)/MonthCount
Answers Post
August 26, 2019
Offline
Dear Sir,
Thank you so much sir for helping me. but i don't know what i am doing wrong.
I am attaching my file, please help me to get Average Head count and attrition.
Your help can save my life.
July 16, 2010
Offline
Hi Hitesh,
No file attached. Please be sure to click 'start upload' after selecting your file.
I think this is a Power Pivot question, not Power Query.
Mynda
August 26, 2019
Offline
Sorry my mistake, attaching file again
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Hitesh,
As mentioned, for the Average you need a month count:
MonthCount:=VAR _start = STARTOFYEAR( ALL('Date'[Date] ))
VAR _end = ENDOFMONTH( 'Date'[Date] )
RETURN DATEDIFF( _start, _end, MONTH )+1
Then, the YTD Avg HC is very simple:
YTD Avg HC:=DIVIDE([Active Head Count], [MonthCount])
Monthly Attrition is basically your formula:
Monthly Attrition:=([YTD_Leavers]/[YTD Avg HC]*12)/[MonthCount]
August 26, 2019
Offline
Dear Sir,
Please help me for above topic, if you have not understand my question than please let me know. i have put excel formula how i want answer, i don't know how to get answer in Power pivot.
Sorry to disturb you but this problem can solve lots of my issue.
Again Thank you so much in advance.
August 26, 2019
Offline
Dear Team,
Please help me to find Average of Active Head count using measures.
| Active Head Count := | CALCULATE( [JoinersCounts]- [leaversCounts],FILTER (ALLSELECTED ('Date'[Date]), ISONORAFTER ('Date'[Date], MAX('Date'[Date]), DESC))) |
| Avg. of Active Head Count | Please help me to find Avg. of Active Head Count for each month example given below. If possible please do calculation in attached excel only. |
1 Guest(s)
