Forum Discussion

Siddiq8686's avatar
Siddiq8686
Icon for Helper I rankHelper I
4 years ago
Solved

Employee Turnover

Hello Everyone,   I am attaching a table below showing month wise employee population and separations (employees who left the organization.   MonthQTRPopulationLeaversTurnover Jan QTR 1 283...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Siddiq8686 
    The final report looks like this

    Make sure you adjust relations like this


    Following are the measures' code:

     

     

    Employee Left = SUM ( Separation_Data4[FTE] )
    Population = 
VAR MonthlyPopulation =
    SUM ( Payroll_Merge_File[FTE] )
VAR QuarterAveragePopulation =
    CALCULATE ( 
        AVERAGE ( Payroll_Merge_File[Quarterly Population] ),
        ALLSELECTED (Calendar_Table ),
        CROSSFILTER ( Calendar_Table[Date], Payroll_Merge_File[Payroll Proces Date], None ),
        USERELATIONSHIP (Calendar_Table[Year Quarter], Payroll_Merge_File[Year Quarter] )
    )
VAR Result = 
    IF (
        HASONEVALUE ( Calendar_Table[Year Month] ),
        MonthlyPopulation ,
        QuarterAveragePopulation 
    )
RETURN
    Result 
    Monthly Turnover = 
DIVIDE (
    [Employee Left],
    [Population]
)

     

     

     

  • Siddiq8686's avatar
    Siddiq8686
    4 years ago

    This solution is working fine

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Siddiq8686 
The final report looks like this

Make sure you adjust relations like this


Following are the measures' code:

 

 

Employee Left = SUM ( Separation_Data4[FTE] )
Population = 
VAR MonthlyPopulation =
    SUM ( Payroll_Merge_File[FTE] )
VAR QuarterAveragePopulation =
    CALCULATE ( 
        AVERAGE ( Payroll_Merge_File[Quarterly Population] ),
        ALLSELECTED (Calendar_Table ),
        CROSSFILTER ( Calendar_Table[Date], Payroll_Merge_File[Payroll Proces Date], None ),
        USERELATIONSHIP (Calendar_Table[Year Quarter], Payroll_Merge_File[Year Quarter] )
    )
VAR Result = 
    IF (
        HASONEVALUE ( Calendar_Table[Year Month] ),
        MonthlyPopulation ,
        QuarterAveragePopulation 
    )
RETURN
    Result 
Monthly Turnover = 
DIVIDE (
    [Employee Left],
    [Population]
)

 

 

 

Siddiq8686's avatar
Siddiq8686
Icon for Helper I rankHelper I
4 years ago

Hello Tamerj1,

 

Thanks for the solution. Now the measure is working perfect. Many Thanks once again.

 

Regards,

Muhammad Siddiq