Forum Discussion
% of Role Utilisation per project
Hi All
need some help. maybe i am looking into it too much but been struggling to come up with a calculation correctly
below is sample data, my main data source has approx 15 different columns but the main data i need to drive from are the 5 columns in sample data
| PROJECT | ROLE | DATE | HOURS | FY |
| Project 1 | BA | 23-Sep-24 | 5 | FY25 |
| Project 1 | PM | 23-Sep-24 | 3 | FY25 |
| Project 1 | PM | 23-Sep-24 | 2 | FY25 |
| Project 1 | Tester | 23-Sep-24 | 2 | FY25 |
| Project 1 | Dev | 23-Sep-24 | 1 | FY25 |
| Project 1 | Admin | 23-Sep-24 | 5 | FY25 |
| Project 1 | BA | 24-Sep-24 | 6 | FY25 |
| Project 1 | PM | 24-Sep-24 | 7 | FY25 |
| Project 1 | Tester | 24-Sep-24 | 6 | FY25 |
| Project 1 | Dev | 24-Sep-24 | 5 | FY25 |
| Project 1 | Admin | 25-Sep-24 | 3 | FY25 |
| Project 1 | BA | 25-Sep-24 | 4 | FY25 |
| Project 1 | BA | 26-Sep-24 | 5 | FY25 |
| Project 1 | PM | 26-Sep-24 | 7 | FY25 |
| Project 1 | Tester | 26-Sep-24 | 3 | FY25 |
| Project 1 | Dev | 28-Sep-24 | 1 | FY25 |
| Project 2 | Dev | 21-Sep-24 | 5 | FY25 |
| Project 2 | PM | 22-Sep-24 | 4 | FY25 |
| Project 2 | BA | 23-Sep-24 | 1 | FY25 |
| Project 2 | Tester | 24-Sep-24 | 6 | FY25 |
| Project 2 | BA | 25-Sep-24 | 7 | FY25 |
| Project 2 | BA | 26-Sep-24 | 4 | FY25 |
| Project 2 | BA | 27-Sep-24 | 5 | FY25 |
| Project 2 | Tester | 28-Sep-24 | 8 | FY25 |
| Project 2 | Admin | 29-Sep-24 | 9 | FY25 |
| Project 2 | BA | 30-Sep-24 | 1 | FY25 |
| Project 2 | Dev | 1-Oct-24 | 2 | FY25 |
| Project 2 | BA | 2-Oct-24 | 1 | FY25 |
| Project 2 | Tester | 3-Oct-24 | 4 | FY25 |
| Project 4 | Dev | 24-Sep-24 | 5 | FY25 |
| Project 4 | Admin | 25-Sep-24 | 3 | FY25 |
| Project 4 | BA | 25-Sep-24 | 4 | FY25 |
| Project 4 | BA | 26-Sep-24 | 5 | FY25 |
| Project 4 | PM | 26-Sep-24 | 7 | FY25 |
| Project 4 | Tester | 26-Sep-24 | 3 | FY25 |
| Project 4 | Dev | 28-Sep-24 | 1 | FY25 |
| Project 3 | Admin | 29-Sep-24 | 9 | FY25 |
| Project 3 | BA | 30-Sep-24 | 1 | FY25 |
| Project 3 | Dev | 1-Oct-24 | 2 | FY25 |
| Project 3 | BA | 2-Oct-24 | 1 | FY25 |
| Project 3 | BA | 3-Oct-24 | 4 | FY25 |
as you can see, a single role can have multiple entries against a project on same or different days
what i am trying to achieve is a calculation on the ultisaltion of the role against the projects in the list and the total and average hours logged for that project and FY
for example in the sample data, the "tester" is not allocated against project 3 but is allocated against projects 1,2 & 4. hence the calculation would be the tester is used across 75% across all total projects
then i am trying to calculate, the average hours for that role for that project.
Essentially wish for the output to look like the below
| Role | Count of times role was used across all Projects | % Allocation across projects | Total hours | Average Hours |
| BA | ||||
| Tester | 3 | 75% | 32 hrs | 10.7 hrs |
| PM | ||||
| Admin |
hopefully this makes sense
Hi Batman_powerbi -can you please create below measures:
DistinctProjectsPerRole = DISTINCTCOUNT(prodj[PROJECT])Total number of projects:TotalProjects = CALCULATE(DISTINCTCOUNT(prodj[PROJECT]), ALL(prodj))%allocation measure as below:PercentAllocation = DIVIDE([DistinctProjectsPerRole], [TotalProjects])Hope this helps. Please check.
5 Replies
- rajendraongole1
Super User
Hi Batman_powerbi -you can calculate the count of times each role is used across projects, their percentage allocation across all projects
measure 1:
Count of Projects per Role =CALCULATE(DISTINCTCOUNT('prodj'[PROJECT]),ALLEXCEPT('prodj', 'prodj'[ROLE]))Measure2 for allocation%% Allocation =DIVIDE([Count of Projects per Role],DISTINCTCOUNT('prodj'[PROJECT]),0)Measure 3:Total Hours per Role =CALCULATE(SUM('prodj'[HOURS]),ALLEXCEPT('prodj', 'prodj'[ROLE]))Measure 4:calculate the average hours each role has spent on each project.Average Hours per Role =
DIVIDE(
[Total Hours per Role],
[Count of Projects per Role],
0
)Hope the above measures helps to create the required report.
rajendraongole1
thankyou for the helpbased on your calculation the Measure 2 is 100%, it should be the calculation against the total of projects
for the tester, as per the example, the correct output is 75% as the "Tester" has been used on 3 out of 4 projects.
another example, in my real data, i have a BA whom has been used across 22 projects out of a possible 43 for the FY which would be a calculation of 51.16%- rajendraongole1
Hi Batman_powerbi -can you please create below measures:
DistinctProjectsPerRole = DISTINCTCOUNT(prodj[PROJECT])Total number of projects:TotalProjects = CALCULATE(DISTINCTCOUNT(prodj[PROJECT]), ALL(prodj))%allocation measure as below:PercentAllocation = DIVIDE([DistinctProjectsPerRole], [TotalProjects])Hope this helps. Please check.
