Forum Discussion
Count completed task by concept
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!
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! 🙂
5 Replies
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! 🙂
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:
%Completed = (CALCULATE(DISTINCTCOUNT(Table[Project]), Table[Status] = "Completed")-(CALCULATE(DISTINCTCOUNT(Table[State]), GROUPBY(Table,Table[Project]))))/CALCULATE(DISTINCTCOUNT(Table[Project]))I basically count the projects with at least one "Completed" task and then substract the projects that are not 100% completed and divide that result by the total of projects. I'm not sure if it will work in all the possible scenarios, so I will use your measure.Thank you so much!!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!
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
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
)
