Forum Discussion

carolinastylin's avatar
carolinastylin
New Member
2 years ago
Solved

Progress Measure

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 

DAX
percentage
progress
task completion
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.