How to calculate average

[JULY1]

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.  🙏🙏

[Greg_Deckler]

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

[JULY1]

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 ID	Business Unit	Result
00001	Marketing	1
00002	Marketing	1
00003	Marketing	2
00004	Marketing	3
00005	Marketing	2
00006	Marketing	4
00007	Marketing	1
00008	Marketing	1
00009	Operations	2
00010	Operations	4
00011	Operations	2
00012	Operations	4
00013	Operations	2
00014	Operations	1
00015	Finance	1
00016	Finance	1
00017	Finance	3
00018	Finance	3
00019	Finance	3
00020	Finance	3

Sample data table 2 - Termination 

Employee ID	Business Unit	Result	Effective Date
00004	Marketing	3	05/01/2024
00006	Marketing	4	15/01/2024
00011	Operations	2	22/02/2024
00017	Finance	3	02/03/2024
00003	Marketing	2	05/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 Unit	Attrition
Finance	17.4%  (1/5.75)
Marketing	32.0% (2/6.25)
Operations	28.6% (1/3.5)
Total	25.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	Feb	Mar	Apr	Average HC
Finance	6	6	6	5	5.75
Marketing	7	6	6	6	6.25
Operations	4	4	3	3	3.5
Total	17	16	15	14	15.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