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 left25% returned to a different job NI_NumberNameTitleDate_JoinedDate_LeftNI112233Dave JonesCustomer Service01/04/202420/05/2024NI112233Dave JonesCustomer Service01/06/202410/06/2024NI884455Eric DaviesCustomer Service01/07/202410/10/2024NI884455Eric DaviesStore Manager20/10/202428/10/2024NI009900Lloyd Williams Store Manager01/04/202410/09/2024NI009900Lloyd Williams Store Manager01/10/202411/11/2024NI887711Ian IansStore Manager10/05/202410/10/2024NI887711Ian IansStore Manager01/11/2024

Forum Discussion

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 different job

NI_Number	Name	Title	Date_Joined	Date_Left
NI112233	Dave Jones	Customer Service	01/04/2024	20/05/2024
NI112233	Dave Jones	Customer Service	01/06/2024	10/06/2024
NI884455	Eric Davies	Customer Service	01/07/2024	10/10/2024
NI884455	Eric Davies	Store Manager	20/10/2024	28/10/2024
NI009900	Lloyd Williams	Store Manager	01/04/2024	10/09/2024
NI009900	Lloyd Williams	Store Manager	01/10/2024	11/11/2024
NI887711	Ian Ians	Store Manager	10/05/2024	10/10/2024
NI887711	Ian Ians	Store Manager	01/11/2024

Need Help

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
11 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

Need help calculating.pbix49 KB

8 Replies

Anonymous
Not applicable
11 months ago
Hi RichOB

Thank you for reaching out to the Microsoft Fabric Forum Community.

GrowthNatives srlabhe ryan_mayu Thanks for your inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.
- Anonymous
  Not applicable
  11 months ago
  Hi RichOB
  
  I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
  - Anonymous
    Not applicable
    11 months ago
    Hi RichOB
    
    Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.
GrowthNatives
Super User
11 months ago
Hi RichOB , you can use the following command to get the required %

DAX Total Rehires = CALCULATE ( DISTINCTCOUNT ( Employees[NI_Number] ), FILTER ( Employees, NOT ISBLANK ( Employees[Date_Left] ) ) ) Same Job Rehires = CALCULATE ( DISTINCTCOUNT ( Employees[NI_Number] ), FILTER ( Employees, CALCULATE ( MIN ( Employees[Title] ), ALLEXCEPT ( Employees, Employees[NI_Number] ) ) = CALCULATE ( MAX ( Employees[Title] ), ALLEXCEPT ( Employees, Employees[NI_Number] ) ) ) ) Different Job Rehires = [Total Rehires] - [Same Job Rehires] Same % = DIVIDE ( [Same Job Rehires], [Total Rehires] ) Different % = DIVIDE ( [Different Job Rehires], [Total Rehires] )
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]
- RichOB
  Post Partisan
  11 months ago
  Hi GrowthNatives, thanks for your reply!
  
  Having the "Different Job Rehires" as Total - Same Jobs is causing some problems.
  
  Would you know what the DAX would be to obtain that number instead, please?
  
  Thanks
  Rich

srlabhe

Super User

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

ryan_mayu
Super User
11 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

Need help calculating.pbix49 KB
Shubham_rai955
Super User
11 months ago
To calculate the percentages shown in the example using your employee return table, create two DAX measures in Power BI:
1. Returned to Same Job (%)
This measure counts employees who left and returned to the same job (same NI_Number and Title, different Date_Joined) and divides by the total number of returned employees.

Returned to Same Job (%) = VAR ReturnedEmployees = SUMMARIZE( 'Table', 'Table'[NI_Number], "ReturnCount", COUNTROWS('Table') ) VAR SameJob = COUNTROWS( FILTER( ADDCOLUMNS( ReturnedEmployees, "DistinctTitles", CALCULATE(DISTINCTCOUNT('Table'[Title]), ALLEXCEPT('Table', 'Table'[NI_Number])) ), [ReturnCount] > 1 && [DistinctTitles] = 1 ) ) VAR TotalReturned = COUNTROWS(FILTER(ReturnedEmployees, [ReturnCount] > 1)) RETURN DIVIDE(SameJob, TotalReturned)
2. Returned to Different Job (%)
This measure counts employees who returned to a different job (same NI_Number, but different Title).

Returned to Different Job (%) = VAR ReturnedEmployees = SUMMARIZE( 'Table', 'Table'[NI_Number], "ReturnCount", COUNTROWS('Table') ) VAR DiffJob = COUNTROWS( FILTER( ADDCOLUMNS( ReturnedEmployees, "DistinctTitles", CALCULATE(DISTINCTCOUNT('Table'[Title]), ALLEXCEPT('Table', 'Table'[NI_Number])) ), [ReturnCount] > 1 && [DistinctTitles] > 1 ) ) VAR TotalReturned = COUNTROWS(FILTER(ReturnedEmployees, [ReturnCount] > 1)) RETURN DIVIDE(DiffJob, TotalReturned)
These measures will yield the percentage of employees who returned to the same job versus a different job as requested.
Ensure the table name in your model matches 'Table'.
Replace 'Table' with your actual table name if different.

Forum Discussion

Need help calculating a %

8 Replies

1. Returned to Same Job (%)

2. Returned to Different Job (%)

Related content

MoM calculation

Calculation Help

Calculate Tooltips

Hour Calculation

Calculating with Filters