Forum Discussion

RichOB's avatar
RichOB
Post Partisan
11 months ago
Solved

Need help calculating a %

Hi, I have a data table of employees who have returned to my company. Using the table below, what measures would show that:   75% returned to the same job that they left 25% returned to a differen...
Need Help
  • srlabhe's avatar
    srlabhe
    11 months ago
    Step 1: Create calculated columns in the Employment History table
    First, create calculated columns to determine the earliest and most recent job title for each employee. This can be done directly in Power BI's Data view. 
    First Job
    This column identifies the employee's very first job based on the earliest Start Date.
     
    dax
    First Job = 
VAR EarliestStartDate = 
    CALCULATE(
        MIN('Employment History'[Start Date]),
        ALLEXCEPT('Employment History', 'Employment History'[Employee ID])
    )
RETURN
    CALCULATE(
        SELECTEDVALUE('Employment History'[Job Title]),
        ALLEXCEPT('Employment History', 'Employment History'[Employee ID]),
        'Employment History'[Start Date] = EarliestStartDate
    )
    Most Recent Job
    This column identifies the employee's most recent job based on the latest Start Date. 
     
    dax
    Most Recent Job = 
VAR LatestStartDate = 
    CALCULATE(
        MAX('Employment History'[Start Date]),
        ALLEXCEPT('Employment History', 'Employment History'[Employee ID])
    )
RETURN
    CALCULATE(
        SELECTEDVALUE('Employment History'[Job Title]),
        ALLEXCEPT('Employment History', 'Employment History'[Employee ID]),
        'Employment History'[Start Date] = LatestStartDate
    )
     
    Step 2: Create measures to count returning employees
    Next, create measures to count the total number of returning employees and filter them by those who returned to the same job versus a different one. 
    Total Returning Employees
    This measure counts all employees with more than one entry in the Employment History table. 
     
    dax
    Total Returning Employees = 
COUNTROWS(
    FILTER(
        VALUES('Employment History'[Employee ID]),
        CALCULATE(COUNTROWS('Employment History')) > 1
    )
)
    Returned to Same Job
    This measure counts returning employees where their first job title matches their most recent job title. 
     
    dax
    Returned to Same Job = 
CALCULATE(
    [Total Returning Employees],
    FILTER(
        'Employment History',
        'Employment History'[First Job] = 'Employment History'[Most Recent Job]
    )
)
    Returned to Different Job
    This measure counts returning employees where their first job title is different from their most recent job title. 
     
    dax
    Returned to Different Job = 
CALCULATE(
    [Total Returning Employees],
    FILTER(
        'Employment History',
        'Employment History'[First Job] <> 'Employment History'[Most Recent Job]
    )
)
     
    Step 3: Create measures for the percentages
    Finally, create the percentage measures using the counts from Step 2. 
    % Returned to Same Job
     
    dax
    % Returned to Same Job = 
DIVIDE(
    [Returned to Same Job],
    [Total Returning Employees]
)
    % Returned to Different Job
     
    dax
    % Returned to Different Job = 
DIVIDE(
    [Returned to Different Job],
    [Total Returning Employees]
)
    By adding these measures to a card or table visual in Power BI, you can display the calculated percentages for returning employees based on their job changes. For more complex calculations involving employee history, see the Walecon blog on mastering employee status tracking with Power BI and DAX. 
  • ryan_mayu's avatar
    ryan_mayu
    10 months ago

    RichOB 

    what if an employee's title from A to B then to A? Will this consider the same job or not the same?

     

    My soluiton consider this situation as a different job.

     

    1. create a column

     

    Column =
    var _last=maxx(FILTER('Table','Table'[NI_Number]=EARLIER('Table'[NI_Number])&&'Table'[Date_Joined]<EARLIER('Table'[Date_Joined])),'Table'[Date_Joined])
    var _title=maxx(FILTER('Table','Table'[NI_Number]=EARLIER('Table'[NI_Number])&&'Table'[Date_Joined]=_last),'Table'[Title])
    return if(ISBLANK(_last),0,if ('Table'[Title]=_title,0,1))
     
     
    then you can create two measures
     
    different =
    var _tbl=SUMMARIZE('Table','Table'[NI_Number],"check",sum('Table'[Column]))
    return COUNTROWS(FILTER(_tbl,[check]<>0))/DISTINCTCOUNT('Table'[NI_Number])
     
    same =
    var _tbl=SUMMARIZE('Table','Table'[NI_Number],"check",sum('Table'[Column]))
    return COUNTROWS(FILTER(_tbl,[check]=0))/DISTINCTCOUNT('Table'[NI_Number])
     
     
    pls see the attachment below
     
     
ryan_mayu's avatar
ryan_mayu
Super User
10 months ago

RichOB 

what if an employee's title from A to B then to A? Will this consider the same job or not the same?

 

My soluiton consider this situation as a different job.

 

1. create a column

 

Column =
var _last=maxx(FILTER('Table','Table'[NI_Number]=EARLIER('Table'[NI_Number])&&'Table'[Date_Joined]<EARLIER('Table'[Date_Joined])),'Table'[Date_Joined])
var _title=maxx(FILTER('Table','Table'[NI_Number]=EARLIER('Table'[NI_Number])&&'Table'[Date_Joined]=_last),'Table'[Title])
return if(ISBLANK(_last),0,if ('Table'[Title]=_title,0,1))
 
 
then you can create two measures
 
different =
var _tbl=SUMMARIZE('Table','Table'[NI_Number],"check",sum('Table'[Column]))
return COUNTROWS(FILTER(_tbl,[check]<>0))/DISTINCTCOUNT('Table'[NI_Number])
 
same =
var _tbl=SUMMARIZE('Table','Table'[NI_Number],"check",sum('Table'[Column]))
return COUNTROWS(FILTER(_tbl,[check]=0))/DISTINCTCOUNT('Table'[NI_Number])
 
 
pls see the attachment below