Forum Discussion
Calculate Attrition Delta for a Report
Hi All
Can you please help me to derive the Attrition Delta formula/Measure in Power BI:
I am working on a employee Attrition report , where my measures are Total active employees,Attriton, and other measures etc like "Attrition rate" Measure (derived formula not direct column) etc calculated at the month level and Service line Level in the view.
Month Level Attrition Rate Value.
| Month/Year | Attrition Rate |
| Jan-22 | 6.34% |
| Feb-22 | 5.30% |
| Mar-22 | 5.89% |
| Apr-22 | 5.73% |
| May-22 | 5.41% |
| Jun-22 | 3.38% |
Attrition rate at the service Line Level when A user selects (ex-Data Analyst) as a service from the dropdown slicer
| Month/Year | Attrition Rate(Service Line level) |
| Jan-22 | 5.06% |
| Feb-22 | 4.85% |
| Mar-22 | 5.66% |
| Apr-22 | 5.74% |
| May-22 | 3.93% |
| Jun-22 | 3.83% |
I am expecting an output with
| Attrition Delta |
| 1.28% |
| 0.44% |
| 0.23% |
| -0.01% |
| 1.48% |
| -0.44% |
I am attaching the screenshot of sample data
When a user select a service line (ex-Data Analyst) from the Service line slicer he should get the Attrition Delta as below:
(Attrition Delta) = (Total attrition rate at the Jan month level)- (attrition rate for the selected Service line level for the Jan month)
i.e (Attrition Delta)= 6.34 -5.06 = 1.28% for January and so on for other months..
As I am new bee, I am not sure how to achieve this logic . I was thinking if we can create 2 variable to store month level attrition rate and other variable to store Serive level ..
Please help on this!
Thanks,
Hi Anonymous
Please tryAttrition Delta = CALCULATE ( [Attrition], ALL ( TableName[Service] ) ) - [Attrition]
6 Replies
- amitchandak
Super User
Anonymous , Create common tables like date/month and service and try to use that
refer
it can work even if service is available in one table
Hi lbendlin , tamerj1 , SpartaBI , RayWu , amitchandak - Can you guys please help with this topic in creating a delta measure:
Calculate Attrition Delta for a Report
- tamerj1
Community Champion
Hi Anonymous
Please tryAttrition Delta = CALCULATE ( [Attrition], ALL ( TableName[Service] ) ) - [Attrition]tamerj1 : Thanks for the reply.
Unfortunately, I cannot use your Measure
Attrition Delta = CALCULATE ( [Attrition], ALL ( TableName[Service] ) ) - [Attrition]
I can include [Service] in your formula as the attrition rate at service level is already available in my view, but i dont have [Attrition] which is Total attriton rate for month level .
My challenge now is how to derive this month level attrition rate so that I fill in your measure.
You might ask me how did I get the the below Attrition rate % numbers for overall, I got them by dragging the month into the view without using the service line in the view.
Attrition rate % is a derived formula which is = (Employees left the company for a month) / (Average of employee closing and opening counts)
Month/Year Attrition Rate Jan-22 6.34% Feb-22 5.30% Mar-22 5.89% Apr-22 5.73% May-22 5.41% Jun-22 3.38% - tamerj1
Anonymous
Why you cannot use the measure? Did you even try? Here is the same formula with corrected measure nameAttrition Delta = CALCULATE ( [Attrition rate %], ALL ( TableName[Service] ) ) - [Attrition rate %]
