Forum Discussion
How to calculate average
Community ChampionAnonymous 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.
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