Progress Measure

[carolinastylin]

I built a progress measure that is working but I'm trying to optimize. I have 5 different project types with varying numbers of tasks.  I wrote a measure to divide the total completed tasks by the number of incomplete tasks to get the percentage completed. The project types are specified in one column. Need help determining the best method to optimize this measure because the “Valuelist” measure is affecting other measures that I’ve created. FYI... The Completed/Incomplete tasks measures are determined by if a date field is blank or not. All data is being pulled from a SharePoint list. There are 15 columns but not all apply to each ProjectType (I much rather filter the columns that apply for the DAX formula for the specific ProjectType). 

Thanks in advance!

Progress = 

IF.EAGER('Table'[ValueList] = {"ProjectType1"},

DIVIDE([COMPLETED_TASKS1],[COMPLETED_TASKS1]+[INCOMPLETE_TASKS1],0)+0,

    IF.EAGER('A6FP_AWAKEN Program Database'[ValueList] = {"ProjectType2"},

DIVIDE([COMPLETED_TASKS2],[COMPLETED_TASKS2]+[INCOMPLETE_TASKS2],0)+0  -- and so on 

[Anonymous · Accepted solution]

Hi @carolinastylin ,

You can try formula like below to create measure:

CompletedTasks_ProjectType1 = 
CALCULATE(COUNTROWS('Table'), 
    'Table'[ProjectType] = "ProjectType1",
    NOT(ISBLANK('Table'[CompletionDate]))
)

CompletedTasks_ProjectType2 = 
CALCULATE(COUNTROWS('Table'), 
    'Table'[ProjectType] = "ProjectType2",
    NOT(ISBLANK('Table'[CompletionDate]))
)

CompletedTasks_ProjectType3 = 
CALCULATE(COUNTROWS('Table'), 
    'Table'[ProjectType] = "ProjectType3",
    NOT(ISBLANK('Table'[CompletionDate]))
)

IncompleteTasks_ProjectType1 = 
CALCULATE(COUNTROWS('Table'), 
    'Table'[ProjectType] = "ProjectType1",
    ISBLANK('Table'[CompletionDate])
)

IncompleteTasks_ProjectType2 = 
CALCULATE(COUNTROWS('Table'), 
    'Table'[ProjectType] = "ProjectType2",
    ISBLANK('Table'[CompletionDate])
)

IncompleteTasks_ProjectType3 = 
CALCULATE(COUNTROWS('Table'), 
    'Table'[ProjectType] = "ProjectType3",
    ISBLANK('Table'[CompletionDate])
)

Progress = 
SWITCH(
    TRUE(),
    MAX('Table'[ProjectType]) = "ProjectType1", 
    DIVIDE([CompletedTasks_ProjectType1], [CompletedTasks_ProjectType1] + [IncompleteTasks_ProjectType1], 0),

    MAX('Table'[ProjectType]) = "ProjectType2", 
    DIVIDE([CompletedTasks_ProjectType2], [CompletedTasks_ProjectType2] + [IncompleteTasks_ProjectType2], 0),

    MAX('Table'[ProjectType]) = "ProjectType3", 
    DIVIDE([CompletedTasks_ProjectType3], [CompletedTasks_ProjectType3] + [IncompleteTasks_ProjectType3], 0),

    BLANK()
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[carolinastylin]

Thanks! I will give this a try...