Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |