cancel
Showing results 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

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)

2 REPLIES 2
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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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 BalanceJan 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

Thank you

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors