Hi, I'm trying to work out some percentages and not getting very far. I have two tables which looks something like this:
(have changed data details for privacy)
Capacity table
| County | Area | Total Capacity |
| Yorkshire | Leeds | 7,200 |
| Yorkshire | York | 3,360 |
| London | Essex | 48,000 |
| Derbyshire | Bakewell | 10,000 |
| Derbyshire | Chesterfield | 10,000 |
| Lancashire | Preston | 40,000 |
| Lancashire | Manchester | 60,000 |
Availability table
| Date | County | Area | Availability |
| 01/01/25 | Yorkshire | Leeds | 1 |
| 01/01/25 | Yorkshire | York | 0 |
| 01/01/25 | London | Essex | 1 |
| 01/01/25 | Derbyshire | Bakewell | 1 |
| 01/01/25 | Derbyshire | Chesterfield | 0 |
| 01/01/25 | Lancashire | Preston | 1 |
| 01/01/25 | Lancashire | Manchester | 0 |
I don't have this table but this is the first part of the calculation I need it to do somehow
| County | Area | Capacity | Availability | Percentage |
| Yorkshire | Leeds | 7,200 | 1 | 68% |
| Yorkshire | York | 3,360 | 0 | 32% |
| London | Essex | 48,000 | 1 | 100% |
| Derbyshire | Bakewell | 10,000 | 1 | 50% |
| Derbyshire | Chesterfield | 10,000 | 0 | 50% |
| Lancashire | Preston | 40,000 | 1 | 40% |
| Lancashire | Manchester | 60,000 | 0 | 60% |
I need the final table to show something like this, so it sums up the percentage availability if the availability equals a '1'.
| County | Percentage total (availability) |
| Yorkshire | 68% |
| London | 100% |
| Derbyshire | 50% |
| Lancashire | 40% |
Up to now all I've managed to do is calculate a percentage by area across all the areas which isn't any help for my future calculation I need to do. Any help would be greatly appreciated!
