How to create measure in Project table to calculate the Progress%OfAllTheTasks related to a Project?

[VivekGupta]

Hi,

I want to create a measure in Project table to calculate the Progress % of all the Tasks related to a Project.

I have 2 tables:

Project table: ProjectId, Name, StartDate, EndDate 

Task table: TaskId, ProjectId, Name, State, StartDate, EndDate (State: New, Active, Completed)

Project table can contain multiple projects. A project can have multiple tasks. Task table can contain multiple projects.

Formula to used for a given project = (Count of Completed Tasks/Count of Total Tasks)*100

Please let me know how to create the measure using the above formula in Project table.

Thanks.

[Anonymous · Accepted solution]

Hi @VivekGupta ,

Based on your description, you can create a measure as follows.

My test table:

Measure = DIVIDE(

                  CALCULATE(COUNT(Task[TaskId]),'Task'[State]="Completed"),

                  CALCULATE(COUNT('Task'[TaskId]),ALL('Task')),0)*100

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[camargos88]

@VivekGupta ,

Try this measure:

Measure:
DIVIDE(
CALCULATE(COUNT(TASK_TABLE[PROJECT_ID]), STATE = "Completed"),
CALCULATE(COUNT(TASK_TABLE[PROJECT_ID]), ALL(TASK_TABLE[STATE])),
0)

[VivekGupta]

Thanks for your reply.

I would like to know if your solution for the measure will work based on the following conditions:

Project table can contain multiple projects. A project can have multiple tasks. Task table can contain multiple projects.

Formula to used for a given project = (Count of Completed Tasks/Count of Total Tasks)*100

Thanks.