Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am trying to create a progress chart, sort of like a burn up chart with reference to weighted milestones.
I have a table with columns including; finish date, percentage complete of the milestones, the weighting of each milestones (weighting), The Calculated Value (Actual), Value of the Project Milestone (As a percentage of the entire project) and a running total of the milestones value (%).
I was hoping to find actual versus planned in this respect but just a tracking of time versus % would be ideal. I created a reference Calendar to try help the process with dates but the more I play the more I am finding myself lost.
Where am I going wrong and what is the better approach to go about it?
,
Your help would be great!
- Dale
Solved! Go to Solution.
Hi @dalenaughton,
You can try to use below measure formula to calculate cumulative complete percent:
Cumulative Complete = DIVIDE ( CALCULATE ( SUM ( Test[Actual after weighting] ), FILTER ( ALLSELECTED ( Test ), [Finish Date] <= MAX ( Test[Finish Date] ) ) ), CALCULATE ( SUM ( Test[Weighting] ), ALLSELECTED ( Test ) ), -1 )
Notice: I turn off the 'concatenate label' option of x axis and modify 'sort by column' property of 'Program Milestone' column to use finish date as sort order.
Regards,
Xiaoxin Sheng
HI @dalenaughton,
Please share some sample data with expected result for test, it is hard clarify your requirement from less information.
How to Get Your Question Answered Quickly
BTW, current power bi can't directly analytics date ranges who define from multiple date columns, maybe you cna consider to expand date range for analysis.
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
Thanks for the reply, still a bit green here.
An example of what I am trying to accomplish is similar to the info below:
I need to convert my data into a % of the total and compare actual versus planned.
The sample data as per below:
Program Milestone | Finish Date | % Complete | Weighting | Actual after weighting | Cumulative (Planned) | Cumulative (Actual) |
PGM: PABX Upgrade - Additional works & equipment review (All sites) | Friday, 1 September 2017 | 100.00% | 2 | 2 | 2.00 | 2.00 |
PGM: PABX Upgrade - High Level Design Brief & BoM - 3 sites | Wednesday, 13 September 2017 | 100.00% | 2 | 2 | 4.00 | 4.00 |
PGM: PABX Upgrade - PO Issue for additional equipment and site works - All sites | Thursday, 14 September 2017 | 100.00% | 2 | 2 | 6.00 | 6.00 |
PGM: PABX Upgrade - Contractor Maintenance Contract Approval | Friday, 15 September 2017 | 100.00% | 4 | 4 | 10.00 | 10.00 |
PGM: PABX Upgrade - Integrate new servers into Network | Tuesday, 19 September 2017 | 100.00% | 4 | 4 | 14.00 | 14.00 |
PGM: PABX Upgrade - Software PO Issue & Ordering | Thursday, 28 September 2017 | 0% | 4 | 0 | 18.00 | 14.00
|
Hi @dalenaughton,
You can try to use below measure formula to calculate cumulative complete percent:
Cumulative Complete = DIVIDE ( CALCULATE ( SUM ( Test[Actual after weighting] ), FILTER ( ALLSELECTED ( Test ), [Finish Date] <= MAX ( Test[Finish Date] ) ) ), CALCULATE ( SUM ( Test[Weighting] ), ALLSELECTED ( Test ) ), -1 )
Notice: I turn off the 'concatenate label' option of x axis and modify 'sort by column' property of 'Program Milestone' column to use finish date as sort order.
Regards,
Xiaoxin Sheng
Excellent, thanks for that @v-shex-msft.
I used similar from https://www.daxpatterns.com/cumulative-total/
Works perfectly. Thank you once again for your help 🙂
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |