To calculate attrition rate in Power BI, follow these steps:
1. Import your data into Power BI and load it into the data model. Ensure that your data includes columns for the employee join date and exit date.
2. Create a new measure by clicking on “New Measure” in the Modeling tab. Use the DAX formula below to calculate the number of employees who left during a specific time period:
Attrition Rate = COUNTROWS(FILTER('YourTable', 'YourTable'[Exit Date] >= MIN('YourTable'[Exit Date]) && 'YourTable'[Exit Date] <= MAX('YourTable'[Exit Date])))
This DAX formula counts the number of rows where the exit date is within the specified time period. 'YourTable' should be replaced with the name of the table containing your employee data, and 'Exit Date' should refer to the column in your table that represents employee exit dates.
3. Create another measure to calculate the total number of employees during the same time period:
Total Employees = CALCULATE(COUNTROWS('YourTable'), ALL('YourTable'[Employee Join Date]))
This DAX formula counts the number of rows in the table, disregarding any filters on the join date column.
4. Finally, create another measure to calculate the attrition rate as a percentage:
Attrition Rate Percentage = DIVIDE([Attrition Rate], [Total Employees])
This DAX formula divides the attrition rate by the total number of employees to obtain the attrition rate as a percentage.
To visualize the attrition rate, you can create a table or chart using the new measures. For example, you could create a line chart that shows the attrition rate over time.
Let's consider an example: Suppose you have a table named "Employee" with columns "Employee ID", "Join Date", and "Exit Date". You can use the above DAX formulas to calculate and visualize the attrition rate of your employees in Power BI.