Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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 , 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)
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:
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!
Proud to be a Super User!
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:
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!
Proud to be a Super User!
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.
@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 , Measure is DAX. So please create a column.
You are creating calculated column in power query
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.
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.
@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)
"
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |