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 ...
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/