Here is picture of needed data.
There is a piece of data (table 1). Primary data starts with March 2010 and I filtered it by Employee 1 and Employee 100.
As you can see Employee 1 was not at the Project in May and December, so in May Total Expenses change was -$700. One part of my task is to show how Total Expenses changes because of structural changes in a team. I created next formulas:
Current Salary:=SUM([Salary])
Salary in Previous Period:=CALCULATE([SalCur]; PREVIOUSMONTH('Calendar'[Date]))
Salary Change:=[SalCur]-[SalPrev]
And everything seems to work fine (table 2) (Grand totals are correct and values for periods when Employee leaves the project are shown) until this moment. Then I write formula, which shows only that values, where Employee comes in or leaves the project. Here it is:
Employee Turnover:=IF(OR([SalCur]-[SalPrev]=-[SalPrev]; [SalPrev]=0); [SalCur]-[SalPrev]; BLANK())
And here is the problem (table 3). If I filter the results by more than 1 employee, grand totals won't sum up correctly, but all values at their place and correclty calculated.
I read on the internet that there is fix for wrong grand totals and I used it.
FIX:=SUMX(VALUES(tbl_Utilization[Employee]); [EmployeeTurnover])
The result (table 4) is that grand totals are correct but values in periods where employee was not at the project are missed. How can I make PowerPivot 'see' negative values? As far as I understand the problem is that there is blank value for Employee 1 salary in May, but can I fix this? Maybe I am using wrong logic to calculate Total Expenses changes because of structural changes if that is the case I am looking forward to the suggestions.
P.S. I am new to Power Pivot
P.S.S I am not native English speaker, so excuse me for grammar mistakes
There is a piece of data (table 1). Primary data starts with March 2010 and I filtered it by Employee 1 and Employee 100.
As you can see Employee 1 was not at the Project in May and December, so in May Total Expenses change was -$700. One part of my task is to show how Total Expenses changes because of structural changes in a team. I created next formulas:
Current Salary:=SUM([Salary])
Salary in Previous Period:=CALCULATE([SalCur]; PREVIOUSMONTH('Calendar'[Date]))
Salary Change:=[SalCur]-[SalPrev]
And everything seems to work fine (table 2) (Grand totals are correct and values for periods when Employee leaves the project are shown) until this moment. Then I write formula, which shows only that values, where Employee comes in or leaves the project. Here it is:
Employee Turnover:=IF(OR([SalCur]-[SalPrev]=-[SalPrev]; [SalPrev]=0); [SalCur]-[SalPrev]; BLANK())
And here is the problem (table 3). If I filter the results by more than 1 employee, grand totals won't sum up correctly, but all values at their place and correclty calculated.
I read on the internet that there is fix for wrong grand totals and I used it.
FIX:=SUMX(VALUES(tbl_Utilization[Employee]); [EmployeeTurnover])
The result (table 4) is that grand totals are correct but values in periods where employee was not at the project are missed. How can I make PowerPivot 'see' negative values? As far as I understand the problem is that there is blank value for Employee 1 salary in May, but can I fix this? Maybe I am using wrong logic to calculate Total Expenses changes because of structural changes if that is the case I am looking forward to the suggestions.
P.S. I am new to Power Pivot
P.S.S I am not native English speaker, so excuse me for grammar mistakes
