topic Looking for help with aggregating data correctly in DAX Commands and Tips

Looking for help with aggregating data correctly

jroob — Fri, 29 Mar 2024 13:38:17 GMT

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?

Re: Looking for help with aggregating data correctly

Gabry — Fri, 29 Mar 2024 16:45:03 GMT

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

Re: Looking for help with aggregating data correctly

Anonymous — Mon, 01 Apr 2024 03:36:51 GMT

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.