Forum Discussion

YoutheshSagar's avatar
YoutheshSagar
New Member
3 years ago

Need help on creating measure

Hi,

 

I have data as shown below.

I Have a Plan table as shown below.

 

My requirement is to create % completion measure based on the below logic ],

Note - Current Phase should use Status=completed only

 

% Completion 
PHASE 
Gap AnalysisGap Analysis (Status=completed) + design + build + QA + UAT + Deployment/total projects
DesignDesign (Status=completed) + build + QA + UAT + Deployment/total projects
BuildBuild (Status=completed) + QA + UAT + Deployment/total projects
QAQA (Status=completed) +  UAT + Deployment/total projects
UATUAT (Status=completed) + Deployment/total projects
DeploymentDeployment (Status=completed)/total projects

1 Reply

  • SamInogic's avatar
    SamInogic
    Icon for Super User rankSuper User
    3 years ago

    Hi YoutheshSagar ,

    As per your requirement, we have created a sample Power BI report with two tables Phases Table, Plan Table. Please refer to the below screenshot.

    Now you want the % completion column according to formulae you provided so you can follow below steps for the same.

    Create a column in Plan Table with below DAX expression which will calculate the count of projects in current Phases table:

    CurrentPhaseProjects = CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Plan Table'[Phase] = 'Phases Table'[Phase]), 'Phases Table'[Status]="Completed")

    Please refer to the below screenshot for the same.

     

     

    Create another column in Plan Table with below DAX expression which will calculate the % Completion based on current Phase in Plan Table:

     

    % Completion =

    SWITCH('Plan Table'[Phase],

    "GAP Analysis", DIVIDE('Plan Table'[CurrentPhaseProjects] + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "Design")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "Build")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "QA")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "UAT")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "Deployment")),COUNTROWS('Phases Table')),

     

    "Design",DIVIDE('Plan Table'[CurrentPhaseProjects] + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "Build")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "QA")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "UAT")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "Deployment")),COUNTROWS('Phases Table')),

     

    "Build",DIVIDE('Plan Table'[CurrentPhaseProjects] + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "QA")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "UAT")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "Deployment")),COUNTROWS('Phases Table')),

     

    "QA",DIVIDE('Plan Table'[CurrentPhaseProjects] + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "UAT")) + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "Deployment")),COUNTROWS('Phases Table')),

     

    "UAT",DIVIDE('Plan Table'[CurrentPhaseProjects] + CALCULATE(COUNTROWS('Phases Table'), FILTER('Phases Table', 'Phases Table'[Phase]= "Deployment")),COUNTROWS('Phases Table')),

     

    "Deployment", DIVIDE('Plan Table'[CurrentPhaseProjects],COUNTROWS('Phases Table'))

    )

    This will result in Completion % for each phase in Plan Table as shown in the below screenshot,


    If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


    Thanks!

    Inogic Professional Service Division

    An expert technical extension for your techno-functional business needs

    Power Platform/Dynamics 365 CRM

    Drop an email at [email protected]

    Service:  http://www.inogic.com/services/ 

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/