Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Experts,
I have a data set like this. This data set is to capture the completion rate of each task on specific days.
I wish to calculate the Overvall Completion Rate by using the last percentage of each task.
In the example above, Overvall Completion Rate = (50%+40%+30%)/3
Is there any expression to create a quick measure to achieve that?
Many thanks!
Best regards,
Tom
Solved! Go to Solution.
Hi @TomLU123
Try this approach
First unpivot your date columns from Query Editor...You will get
Now you can use this MEASURE
[Measure ] = AVERAGEX ( VALUES ( Table1[Task] ), VAR lastnonblankdate = CALCULATE ( MAX ( Table1[Date] ), Table1[Value] <> BLANK () ) RETURN CALCULATE ( SUM ( Table1[Value] ), Table1[Date] = lastnonblankdate ) )
Hi @TomLU123
Try this approach
First unpivot your date columns from Query Editor...You will get
Now you can use this MEASURE
[Measure ] = AVERAGEX ( VALUES ( Table1[Task] ), VAR lastnonblankdate = CALCULATE ( MAX ( Table1[Date] ), Table1[Value] <> BLANK () ) RETURN CALCULATE ( SUM ( Table1[Value] ), Table1[Date] = lastnonblankdate ) )
Hi Zubair, it works! Thank you so much for the help!
Hi Zubair,
Just a further check, 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
See attached file as well
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |