Forum Discussion

Siddiq8686's avatar
Siddiq8686
Helper 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

Siddiq8686's avatar
Siddiq8686
Helper I
4 years ago

and if i need to calculate the turnover of employee for a particular year then below calculation needs to be done;

 

Total separation of the years / (( employees population of Jan + employees population of April+employees                                                                   population of July+ employees population of Oct)/4)

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Siddiq8686 

Please find sample file with solution here https://www.dropbox.com/t/Ojbaa8cK7U2b6goZ

Your report looks like this

First you need to have two relationships between the Date table and your table, one at "Year Month" granularity and the other at "Year Quarter" granularity 

Then use this code to deactivate the non-required relationship and activate the required one. Also something needed to be done at the grand total level (which is the calculation you need for multiquarter selection)

Montly Turnover =
VAR Leavers =
    SUM ( 'Table'[LeaversTurnover] )
VAR MonthlyPopulation =
    CALCULATE (
        AVERAGE ( 'Table'[Population 1st Month of QTR] ),
        CROSSFILTER ( 'Date'[Year Month], 'Table'[Year Month], NONE ),
        USERELATIONSHIP ( 'Date'[Year Quarter], 'Table'[Year Quarter] )
    )
VAR TotalPoulation =
    SUM ( 'Table'[Population 1st Month of QTR] )
VAR Result =
    IF (
        HASONEVALUE ( 'Date'[Year Month] ),
        DIVIDE ( Leavers, MonthlyPopulation ),
        DIVIDE ( Leavers, TotalPoulation )
    )
RETURN
    Result

Please let me know if I solved your problem. If so kindly mark this reply as accepted solution. Thank you

  • tamerj1's avatar
    tamerj1
    Community 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
      Helper I
      4 years ago

      Hello Tamerj1,

       

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

       

      Regards,

      Muhammad Siddiq

    • Siddiq8686's avatar
      Siddiq8686
      Helper I
      4 years ago

      Hello Tamerj1,

       

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

       

      Regards,

      Muhammad Siddiq