Forum Discussion
Grouped Performance Evaluation
Hi All,
I have a dataset that is broken down by projects and stages. What I would like to do is to performance an evaluation on the total project to see whether the budget has been exceeded overall.
The dataset looks as follows:
Project Number | Stage | Budget | Cost | Project Performance |
| Project 1 | S1 | 100 | 200 | Over Budget |
| Project 1 | S2 | 150 | 100 | Over Budget |
| Project 1 | S3 | 100 | 125 | Over Budget |
| Project 1 | S4 | 100 | 110 | Over Budget |
| Project 1 | S5 | 100 | 100 | Over Budget |
| Project 2 | S1 | 200 | 300 | Within Budget |
| Project 2 | S2 | 250 | 100 | Within Budget |
| Project 2 | S3 | 250 | 100 | Within Budget |
| Project 2 | S4 | 250 | 150 | Within Budget |
| Project 2 | S5 | 250 | 100 | Within Budget |
| Project 3 | S1 | 100 | 100 | Encroaching Budget |
| Project 3 | S2 | 125 | 100 | Encroaching Budget |
| Project 3 | S3 | 100 | 100 | Encroaching Budget |
| Project 3 | S4 | 150 | 150 | Encroaching Budget |
| Project 3 | S5 | 100 | 100 | Encroaching Budget |
As you can see, the far right side column would be populated based on the SUM of the total Budget and Cost and then it would be repeated for all stages regardless if any of the individual stages we not consistent with the overall evaluation.
The %'s for the Peformance are evaluated as follows:
0% to < 85% = Within Budget
>= 85% to 100% = Encroaching Budget
> 100% = Over Budget
I did try this in a DAX Measure but it's not producing the desired output.
In Power Query, use the Group By on Proj Number with 3 aggregations (Sum Budget, Sum Cost and 'all rows' )
You can then add a column to divide cost/budget. Then add another column using this to get the bucketing (if..then.. else).
You can then expand the 'all rows' to return the stage details (from the column header)
2 Replies
In Power Query, use the Group By on Proj Number with 3 aggregations (Sum Budget, Sum Cost and 'all rows' )
You can then add a column to divide cost/budget. Then add another column using this to get the bucketing (if..then.. else).
You can then expand the 'all rows' to return the stage details (from the column header)
Thank you for your assistance. Your response has steered me in a direction which allowed me to achieve the desired end result.
