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

JanQTR 12830331 
FebQTR 14475266 
MarchQTR 14428291 
AprilQTR 23150442 
MayQTR 23127308 
JuneQTR 24852310 
JulyQTR 32438316 
AugQTR 32779357 
SeptQTR 34725295 
OctQTR 43527338 
NovQTR 44756464 
DecQTR 44835320 

 

I need to make a DAX measure to calculate the employee turnover as per below formula.

 

For montly turnover:

 

No of Separation for respective month  / employee population of 1st month of respective quarter

 

for exmaple is i need to calculate the turnover for the month of June then i need to divide the separations of june by the employee population of April, as april is the first month of quarter 2. 

 

The same formula will be used if i need to calculate the turnover of employee from jan to June then i will divide the sum of separation from jan to june by the avergae population of Jan plus April.

 

Can any one help me pleaase.

 

 

  • 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]
)

     

     

     

6 Replies

  • 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]
)