Calculate the average completion rate

[TomLU123]

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

[Zubair_Muhammad · Accepted solution]

@TomLU123

File attached as well

[Zubair_Muhammad]

@TomLU123

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 · Accepted solution]

@TomLU123

File attached as well

[TomLU123]

@Zubair_Muhammad

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 · Accepted solution]

@TomLU123

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