Forum Discussion
TomLU123
Helper III
Helper IIICalculate the average completion rate
Dear Experts, I have a data set like this. This data set is to capture the completion rate of each task on specific days. But there are empty values since the user havn't update the progress. ...
We can use MAX instead of LASTDATE
i.e.
Measure = VAR Sumofpercentages = SUMX ( VALUES ( Table1[Task] ), VAR Last_Checkpoint = CALCULATE ( MAX ( Table1[Check Point] ), Table1[Value] <> BLANK () ) RETURN CALCULATE ( SUM ( Table1[Value] ), Table1[Check Point] = Last_Checkpoint ) ) VAR CountofTasks = COUNTROWS ( VALUES ( Table1[Task] ) ) RETURN Sumofpercentages / CountofTasks
Zubair_Muhammad
Community Champion
Community Champion
TomLU123Helper III
Helper III
Dear Zubair,
Many thanks for your great help!
Just a further check: what if the data set is using the Check Point Number instead of the Date. How should we modify the fomular to achieve the calculation?
| Task | Check Point | Value |
| Task 1 | 1 | 30% |
| Task 1 | 2 | 40% |
| Task 1 | 3 | |
| Task 2 | 1 | 40% |
| Task 2 | 2 | |
| Task 2 | 3 | |
| Task 3 | 1 | |
| Task 3 | 2 | |
| Task 3 | 3 |
In the example above, Overvall Completion Rate = (40%+40%)/3
Many thanks!
Best regards,
Tom
- Zubair_Muhammad
We can use MAX instead of LASTDATE
i.e.
Measure = VAR Sumofpercentages = SUMX ( VALUES ( Table1[Task] ), VAR Last_Checkpoint = CALCULATE ( MAX ( Table1[Check Point] ), Table1[Value] <> BLANK () ) RETURN CALCULATE ( SUM ( Table1[Value] ), Table1[Check Point] = Last_Checkpoint ) ) VAR CountofTasks = COUNTROWS ( VALUES ( Table1[Task] ) ) RETURN Sumofpercentages / CountofTasks