Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
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/
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |