Grouped Performance Evaluation

[Sime]

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.

Project Budget Performance =

SWITCH (

TRUE (),

SUM('Project Financial View'[total_project_spent]) / SUM('Project Financial View'[overall_fee]) < 0.85, "Within Budget",

SUM('Project Financial View'[total_project_spent]) / SUM('Project Financial View'[overall_fee]) >= 0.85, "Encroaching Budget",

SUM('Project Financial View'[total_project_spent]) / SUM('Project Financial View'[overall_fee]) > 1.0, "Over Budget"

)

If I could achieve this in PowerQuery it would be preferrable.

Thank you.

[HotChilli · Accepted solution]

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)

[Sime]

Thank you for your assistance. Your response has steered me in a direction which allowed me to achieve the desired end result.