Forum Discussion
calculate participation rate
I have the Case ID by team by cluster in one file and another actual headcount of sales person by team by cluster in another file. I have already create the relationship by linking team name.
I would like to find out the participation rate of each team. However i cannot seems to get the correct figure.
Details as follow for your help
File 1 - case id by team
| case id | team | cluster |
| 1 | clementi | cbd |
| 2 | centre 1 | cbd |
| 3 | clementi | cbd |
| 4 | tpy | cbd |
| 5 | thiong | cbd |
| 6 | thiong | cbd |
| 7 | jurong | west |
| 8 | jurong | west |
| 9 | sun plaza | north east |
| 9 | sun plaza | north east |
| 10 | paya | east |
File 2 -
| actual headcount | team | cluster |
| 9 | clementi | cbd |
| 2 | centre 1 | cbd |
| 4 | tpy | cbd |
| 10 | thiong | cbd |
| 7 | jurong | west |
| 6 | sun plaza | north east |
| 5 | paya | east |
The output i wish to get is as per below
| cluster | case count | participation rate |
| cbd | 6 | 24% |
| clementi | 2 | 22% |
| centre 1 | 1 | 50% |
| tpy | 1 | 25% |
| thiong | 2 | 20% |
| west | 2 | 29% |
| jurong | 2 | 29% |
| north east | 2 | 33% |
| sun plaza | 2 | 33% |
| east | 1 | 20% |
| paya | 1 | 20% |
| total | 11 | 26% |
Thank you.
1 Reply
- wdx223_Daniel
Community Champion
=DIVIDE(COUNTROWS(Table1),SUM(Table2[actual headcount]))
