Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I think this is pretty easy but I just can't get it. I have this data:
Project Task Status %Completed
A A1 "Completed" 50%
A A2 "Completed" 50%
B B1 "Completed" 50%
B B2 NA
C C1 "Completed" 50%
C C2 "In Progress" -
I want to get the percentage of how many projects were completed, something like
#Projects FULL completed(evaluating if all the task get "Completed" in the status column) / #Total projects
The result in this example will be = 1 (Project A with tasks A1 and A2 Completed)/3(Projects A,B,C)
I have used this measure:
but it only returns the number of projects with "Completed" status in any task even if not all the tasks have a "Completed" status.
Do you have any solutions?
Regards!
Solved! Go to Solution.
Hi @csfemco ,
Try this measure, it might seem complex but it does the trick 🙂
% Full Completed Projects =
VAR _tmpTable = ADDCOLUMNS(VALUES( Projects[Project ] ), "IsCompleted",
VAR _curProject = [Project ]
RETURN
IF(COUNTROWS(FILTER(Projects, Projects[Project ] = _curProject && Projects[Status ] <> "Completed")) =0, TRUE, FALSE))
VAR _projectsCompeted = COUNTROWS(FILTER(_tmpTable, [IsCompleted] = TRUE()))
VAR _totalProjects = COUNTROWS(_tmpTable)
RETURN
DIVIDE(_projectsCompeted, _totalProjects)
Basically, I create a table with all project names and see if they are completed or not (if a project has a row with something other then "completed", then at least one task is not completed. Then I count the 'true' rows and all rows and divide those two numbers.
Does this help you? Let me know!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Maybe something like this... (although I'd probably break it up into several measures (e.g. Projects Completed, Total Projects, % Projects Completed, etc)...
Projects Completed =
VAR __numProjects =
DISTINCTCOUNT( 'tbl'[Project] )
VAR __numProjects_Completed =
SUMX(
ALLSELECTED( 'tbl'[Project] ),
VAR __numTasks =
CALCULATE(
DISTINCTCOUNT( 'tbl'[Task] )
)
VAR __numTasks_Completed =
CALCULATE(
DISTINCTCOUNT( 'tbl'[Task] ),
'tbl'[Status] = "Completed"
)
RETURN
IF(
__numTasks = __numTasks_Completed,
1,
0
)
)
RETURN
DIVIDE(
__numProjects_Completed,
__numProjects
)
Hi @csfemco
create 2 measures
isFullComplete =
var _countTasks = CALCULATE(COUNTROWS('Table');ALLEXCEPT('Table';'Table'[Project]))
var _countTasksCompleted = CALCULATE(COUNTROWS('Table');ALLEXCEPT('Table';'Table'[Project]);'Table'[Status]="Completed")
RETURN
IF(_countTasks=_countTasksCompleted; TRUE(); FALSE())
and then
% Completed =
DIVIDE(
CALCULATE(DISTINCTCOUNT('Table'[Project]);FILTER(ALL('Table');[isFullComplete]=TRUE()));
CALCULATE(DISTINCTCOUNT('Table'[Project]);ALL('Table'))
)
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi @csfemco ,
Try this measure, it might seem complex but it does the trick 🙂
% Full Completed Projects =
VAR _tmpTable = ADDCOLUMNS(VALUES( Projects[Project ] ), "IsCompleted",
VAR _curProject = [Project ]
RETURN
IF(COUNTROWS(FILTER(Projects, Projects[Project ] = _curProject && Projects[Status ] <> "Completed")) =0, TRUE, FALSE))
VAR _projectsCompeted = COUNTROWS(FILTER(_tmpTable, [IsCompleted] = TRUE()))
VAR _totalProjects = COUNTROWS(_tmpTable)
RETURN
DIVIDE(_projectsCompeted, _totalProjects)
Basically, I create a table with all project names and see if they are completed or not (if a project has a row with something other then "completed", then at least one task is not completed. Then I count the 'true' rows and all rows and divide those two numbers.
Does this help you? Let me know!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi @JarroVGIT , It works perfectly!
Before your answer I created another measure and it gave me the same results but it was kind of confusing:
Good to hear, glad I could help 🙂 Dont forget to mark my answer as the solution so others may find it easily as well!
Have a good night!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |