Forum Discussion

csfemco's avatar
csfemco
Frequent Visitor
6 years ago
Solved

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"      ...
Need Help
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    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's avatar
csfemco
Frequent Visitor
6 years ago

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's avatar
JarroVGIT
Resident Rockstar
6 years ago

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!