Find positive and negative bookings with the same ID and then add them together

[Anonymous]

Hi all,

I have a table that looks like this:

...	Project Number	...	Project Revenue	...	...
...	B56789	...	1000000	...	...
...	B02020	...	23546	...	...
...	B07897	...	67652	...	...
...	B56789	...	-1000000	...	...

As you can see, there are two Projects with the same number, and one booking has the exact same amount, only negative. That is because in the system, you cannot remove bookings, only delete them in this way if the project is canceled.

What I want to do is to find the matching projects (match: same project number, and exactly opposite revenue).

Then I do not want to remove these rows, but I want to add them together, so they look like this:

This way, the information on how many projects this affects can be retained.

...

B56789

...

0

...

...

Also, it is crucial to not check for absolute matches, but that they are exactly positive and negative, because it is possible to have the same project number twice, with two positive bookings.

Any help would be greatly appreciated! Thank you.

[mpicca13 · Accepted solution]

have you tried creating a simple sum measure. Im assuming you want the project revenue to sum. So just create measure: total revenue:= sum(Project Revenue).

This would then equate to 0 for entries with both positive and negative values of the same whole number.

Then in your table visual put project number and total revenue measure as your inputs.

[amitchandak · Accepted solution]

@Anonymous , This will give a new column with flag =1 for such transactions

flag=

if(isblank(countx(filter(Table, [Project Number] = earlier([Project Number]) && [Project Revenue] = -1*earlier([Project Revenue])),[Project Number] )),blank(),1)

[Sumanth_23 · Accepted solution]

hi @Anonymous - If the project cost column is a currency column when you put the same in a table visual it will automatically add the values as per records available for the proect

The same would apply even if you create a measure for Sum of Project cost

Please see below the approach below: 

Project_Cost = SUM( ProjectExpenses[Project Cost] )

Please mark the above comment as a solution to help others find it more quickly. Also please provide a 👍 if my comment helped with solving your issue. Thanks!

[Anonymous]

@Sumanth_23 

Hi Sumanth, you are absolutely right.

However there is a third column "Costumer Name", and because of bad data in the original system, the names can be a bit different, so the smart summation is not working (eg: "Microsoft", "Microsoft Germany"). Is there a way to ignore differences in the name? I guess not.

Sadly the formula of @amitchandak is not working for me.

[amitchandak · Accepted solution]

@Anonymous , This will give a new column with flag =1 for such transactions

flag=

if(isblank(countx(filter(Table, [Project Number] = earlier([Project Number]) && [Project Revenue] = -1*earlier([Project Revenue])),[Project Number] )),blank(),1)

[Anonymous]

@amitchandak 

Looks promising, but I am getting this error. Maybe I did something wrong

[amitchandak]

@Anonymous , Measure is DAX. So please create a column.

You are creating calculated column in power query

[mpicca13 · Accepted solution]

have you tried creating a simple sum measure. Im assuming you want the project revenue to sum. So just create measure: total revenue:= sum(Project Revenue).

This would then equate to 0 for entries with both positive and negative values of the same whole number.

Then in your table visual put project number and total revenue measure as your inputs.

[Anonymous]

Thanks for the answer. However I dont want to sum the project revenue. In the end I want to sort the projects by revenue.

Here is the crucial point. If i sort them by revenue without the discribed step, i will see some projects on top with a high revenue. However, they are cancelled, but i wouldnt see that because the negative projects are at the bottom. Also, there are around 5000+ projects.

[amitchandak]

@Anonymous , Check my suggestion made at the same time as your update.

"

This will give a new column with flag =1 for such transactions

flag=

if(isblank(countx(filter(Table, [Project Number] = earlier([Project Number]) && [Project Revenue] = -1*earlier([Project Revenue])),[Project Number] )),blank(),1)

"