Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JULY1
Helper I
Helper I

How to calculate average

Hi,

 

I have 2 sheets,

Sheet 1 – Result of evaluation – there is only 1 record per 1 person, and everyone has the evaluation result on the same date – 31/01/24

Example of raw data

Units

Result of evaluation

Date

Marketing

1

31/01/24

Marketing

3

31/01/24

Finance

2

31/01/24

Finance

2

31/01/24

Finance

3

31/01/24

Operations

3

31/01/24

Operations

3

31/01/24

Operations

4

31/01/24

 

Sheet 2- List of resign employees – which shows the ‘Result of evaluation’ that was merged from the sheet 1.

Example of raw data

Units

Employee ID

Result of evaluation

Terminate Date

Marketing

1111

1

31/01/24

Finance

3333

2

15/03/24

 

There is no relationship between these 2 sheets because once I create the relationship it said that it will cause ambiguity.

 

And the one result that I’ve been longing for is to calculate the average of Result of evaluation so that I can calculate the attrition rate after this.

This is what I have on Excel

 

Sum of Result of evaluation 1-3 (4 was not counted)

January - number of terminations in Jan

No termination in February

March - number of terminations in March

 

Units

January

February

March

April

Average

Marketing

2

1

1

1

1.25

Finance

3

3

3

2

2.75

Operations

2

2

2

2

2

 

So after I got the average I can calculate the attrition rate like this (number of terminations / average)

Units

January

February

March

April

Marketing

50% (1/2)

67% (1/1.5)

75% (1/1.33)

80% (1/1.25)

Finance

0%

0%

33% (1/3)

36% (1/2.75)

Operations

0%

0%

0%

0%

 

But now the current measure I got brings me the wrong answers, it's only correct when at the end of the year (e.g. only the attrition rate in December is correct)

 

So please help suggest how to calculate the attrition like above table.

 

Thank you in advance.  🙏🙏

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

@JULY1 I'm not quite following this. I get the 2 for Marketing for January. I don't get the rest of the columns. You have a February month but this lists the number of terminations that happened in January? Where are the other 2 columns coming from (no terminations in February for March and number of terminations in march for "April". Thoroughly confused.

 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Sorry for my confused explanation.

Let me explain again

 

1. Sample data as text, use the table tool in the editing bar

Sample data table 1 - Total Headcount

Employee IDBusiness UnitResult
00001Marketing1
00002Marketing1
00003Marketing2
00004Marketing3
00005Marketing2
00006Marketing4
00007Marketing1
00008Marketing1
00009Operations2
00010Operations4
00011Operations2
00012Operations4
00013Operations2
00014Operations1
00015Finance1
00016Finance1
00017Finance3
00018Finance3
00019Finance3
00020Finance3

 

Sample data table 2 - Termination 

Employee IDBusiness UnitResultEffective Date
00004Marketing305/01/2024
00006Marketing415/01/2024
00011Operations222/02/2024
00017Finance302/03/2024
00003Marketing205/04/2024

 

There are no relationship between table 1 and table 2

but table 1 and table 2 have relation with the Business Unit that was set as a primary key (all unique)

and table 2 has relation with date table 

 

2. table 3 - Expected result 

Business UnitAttrition 
Finance

17.4%  (1/5.75)

Marketing32.0% (2/6.25)
Operations28.6% (1/3.5)
Total25.8% (4/15.5)

 

 

3. Explanation in words of how to get the result from 1. to 2.

- The attrition from table 3 is calculated from Total of termination (table 2) / Average headcount (that will be calculated from table 1)

- Average headcount is calculated from table 1

for example:

 Beginning Balance
Jan
FebMarAprAverage HC
Finance66655.75
Marketing76666.25
Operations44333.5
Total1716151415.5

(I can't find the solutions to calculate this table, this table is calculated on Excel)

 

Beginning Balance is summarized from table 1 with filter only the result 1-3 (excluded 4)

and then there was 1 person who got the result 3 in marketing resigned in January so the total headcount in Feb decreased from 7 to 6.

and then, another person in Operations left in February, so total headcount in March was 3. 

So if I can find the average headcount then I can find the attrition that is calculated from termination / average headcount

 

Please let me know if you need more information

 

Thank you

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors