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. 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
Solved! Go to Solution.
File attached as well
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
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
File attached as well
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
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
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |