Need help on creating measure

[YoutheshSagar]

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 Analysis	Gap Analysis (Status=completed) + design + build + QA + UAT + Deployment/total projects
Design	Design (Status=completed) + build + QA + UAT + Deployment/total projects
Build	Build (Status=completed) + QA + UAT + Deployment/total projects
QA	QA (Status=completed) +  UAT + Deployment/total projects
UAT	UAT (Status=completed) + Deployment/total projects
Deployment	Deployment (Status=completed)/total projects

[SamInogic]

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 crm@inogic.com

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

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