Forum Discussion

Spencer's avatar
Spencer
Icon for Helper II rankHelper II
10 years ago
Solved

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. ...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    Spencer

     

    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]))
)
v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

Spencer

 

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]))
)
Spencer's avatar
Spencer
Icon for Helper II rankHelper II
10 years ago

That'll be ideal, huge thanks v-sihou-msft