Forum Discussion

Batman_powerbi's avatar
Batman_powerbi
Frequent Visitor
1 year ago
Solved

% 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 column...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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.

 
 
Batman_powerbi's avatar
Batman_powerbi
Frequent Visitor
1 year ago

rajendraongole1 

thankyou for the help

 

based 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's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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.

    • Batman_powerbi's avatar
      Batman_powerbi
      Frequent Visitor
      1 year ago

      rajendraongole1 
      thankyou

      i tried using variables to simplify the amount of measures to create
      when i do this i am getting a rounded up number rather than a true %, not sure what i am doing wrong

       

      Measure 2 =
      VAR A = DISTINCTCOUNT('Data'[Project Name])
      VAR B = CALCULATE(DISTINCTCOUNT('Data'[Project Name]), ALL('Data'))

      RETURN
      DIVIDE(A,B)
       

       

    • Batman_powerbi's avatar
      Batman_powerbi
      Frequent Visitor
      1 year ago

      rajendraongole1 
      not sure exactly what was happening with the rounded numbers, but started fresh and all works as intended

       

      thankyou for the help