Calculate Attrition Delta for a Report

[Anonymous]

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,

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please try

Attrition Delta =
CALCULATE ( [Attrition], ALL ( TableName[Service] ) ) - [Attrition]

[Anonymous]

@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 name

Attrition Delta =
CALCULATE ( [Attrition rate %], ALL ( TableName[Service] ) ) - [Attrition rate %]

[Anonymous]

Hi @lbendlin , @tamerj1 , @SpartaBI , @ray_ux , @amitchandak  - Can you guys please help with this topic in creating a delta measure:

Calculate Attrition Delta for a Report

[amitchandak]

@Anonymous , Create common tables like date/month and service and try to use that

refer

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bda

it can work even if service is available in one table

[Anonymous]

Hi Amit,

Unfortunately I cannot create a common table as of now due to restricted access and time frame. 

Can you please help me to give an alternative solution to create Delta logic with the scenario. Thanks