Count completed task by concept

[csfemco]

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:

#Completed = CALCULATE(DISTINCTCOUNT(table[Project]), Table[Status] = "Completada")

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! 

[SQLbyoBI]

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
    )

[az38]

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

[JarroVGIT · Accepted 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! 🙂

[csfemco]

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!!

[JarroVGIT]

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!