Forum Discussion
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.
| Task | Date | Value |
| Task 1 | 1-Jun-18 | 30% |
| Task 1 | 1-Jul-18 | 40% |
| Task 1 | 1-Aug-18 | |
| Task 2 | 1-Jun-18 | 40% |
| Task 2 | 1-Jul-18 | |
| Task 2 | 1-Aug-18 | |
| Task 3 | 1-Jun-18 | |
| Task 3 | 1-Jul-18 | |
| Task 3 | 1-Aug-18 |
I wish to calculate the Overvall Completion Rate by using the last percentage of each task (including the empty one).
In the example above, Overvall Completion Rate = (40%+40%)/3
Is there any expression to create a quick measure to achieve that?
Many thanks!
Best regards,
Tom
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
4 Replies
- Zubair_Muhammad
Community Champion
This MEASURE please
My apologies.. I saw your notification then forgot to reply
Measure = VAR Sumofpercentages = SUMX ( VALUES ( Table1[Task] ), VAR Last_date = CALCULATE ( LASTDATE ( Table1[Date] ), Table1[Value] <> BLANK () ) RETURN CALCULATE ( SUM ( Table1[Value] ), Table1[Date] = Last_date ) ) VAR CountofTasks = COUNTROWS ( VALUES ( Table1[Task] ) ) RETURN Sumofpercentages / CountofTasks- Zubair_Muhammad
TomLU123Dear 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