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

SQLbyoBI's avatar
SQLbyoBI
Advocate I
6 years ago

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
    )