Forum Discussion
Sum - Headcount
Hi
I'm trying to create a calculated column which gives a current headcount at the end of each month.
I've managed to create a table which has the amount of starters and leavers for each month. A result of this is that those without a leave date (i.e. still an active employee) have been lumped into one number totalling 313. And I think this is why I can't simply sum the Net column to get the current headcount at the end of each month.
Thanks and any help greatly appreciated!MonthlyStartersAndLeavers Table
I assume you use "merge queries" with full join to create this table so that those "not match" rows will aggregate together.
In this scenario, I suggest you filter those "not match" rows in your table. You can filter empty rows in Query Editor.
If you don't want to modify the table, you can add filter expression in your calculation.
Headcount = CALCULATE( SUM(MonthlyStartersAndLeavers[Net]), FILTER ( ALL ( 'MonthlyStartersAndLeavers'[Months] ), 'MonthlyStartersAndLeavers'[Months]<>BLANK() ), FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]<=MAX(DateTable[DateKey])) )
7 Replies
I would create a Month column, something like MONTH([MonthS]) and then edit your column so that [Net] is per row. Then, you could just create a table, place [Month] and [Net] in the table and you should get your answer.
Thanks for the response Greg_Deckler.
Your solution should work out but is there anyway to keep this within my current table?
or, off the cuff, how about at the Visual level doing a running total of the Net column?
Thanks for the suggestion CahabaData, but I'm going to need to be using the headcount to create other measures in the future.
Alternatively I've calculated the headcount as a measure, using the below formula, but the -313 is still taken into account.
Headcount = CALCULATE(SUM(MonthlyStartersAndLeavers[Net]),FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]<=MAX(DateTable[DateKey])))
I assume you use "merge queries" with full join to create this table so that those "not match" rows will aggregate together.
In this scenario, I suggest you filter those "not match" rows in your table. You can filter empty rows in Query Editor.
If you don't want to modify the table, you can add filter expression in your calculation.
Headcount = CALCULATE( SUM(MonthlyStartersAndLeavers[Net]), FILTER ( ALL ( 'MonthlyStartersAndLeavers'[Months] ), 'MonthlyStartersAndLeavers'[Months]<>BLANK() ), FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]<=MAX(DateTable[DateKey])) )That'll be ideal, huge thanks v-sihou-msft
