Looking for help with aggregating data correctly

[jroob]

I've got a dataset that has the following columns, on mobile so apologies for formatting

Example of data:

ProjectID | OriginalBudget | NewAmount | IsChangeOrder

123 | $100 | $20 | Yes

123 | $100 | $50 | Yes

456 | $200 | $50 | Yes

One question being asked is what percentage of the project budgets overall are change orders?

The problem I'm running into is when I try to do a measure that divides the sum of the NewAmount by the OriginalAmount is that the OriginalAmount is being added over and over since it exists in every row since one project can have many change orders.

Is there a simple way to do this measure?

[Anonymous]

Hi, @jroob 

First, create a measure that sums up only once for each budget:

Total Unique Original Budget = 
SUMX(
    SUMMARIZE('Table', 'Table'[ProjectID], "UniqueBudget", MAX('Table'[OriginalBudget])),
    [UniqueBudget]
)

This measure summarizes all values:

Total New Amount = SUM('Table'[NewAmount])

Calculate the percentage:

Percentage of Budget as Change Orders = 
DIVIDE([Total New Amount], [Total Unique Original Budget])

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gabry]

Hello,

yes, here is your solution:

this is your measure:

Measure =

 var original= MAX('Table'[OriginalBudget ])

 var new= SUM('Table'[new])

 var tot = new+original

 RETURN

DIVIDE(new,tot)

Let me know.

bye