Subtract one row from another based on a common ID and different cost categories

Here is my basic problem. An Item can have multiple rows if it has multiple Cost Types.

I want to setup a formula that finds those rose that have multiple Cost Types and subtract A from B and replace the amount for A with the result or create a new row (call it A-B) and get rid of the original A and B rows for an item. I want to keep all As that don't have a corresponding B and all Cs.

What would the formula (or multi-field formula) look like?

Thank you for your assistance.

Item	Cost Type	Amount
99999	A	235253
10001	A	32423
10001	B	243523
10001	C	3235
54321	A	154325

Preparation

Expression

Accepted answers

derekbelyea

Here is an alternative approach to solving this.

ResolveCostTypes.yxmd

danrh

This should get you there:

The Cross Tab puts the different cost types in different columns. Then a simple formula produces the result you're looking for, a Transpose to put the data back in it's original form, and a filter to get rid of the excess rows.

Hope it helps!

solution.yxmd

All comments

john_miller9

Here's one way to solve this

Subtract Rows Solution.PNG Subtract Rows Output.PNG

Subtract Rows.yxzp

derekbelyea

Here is an alternative approach to solving this.

ResolveCostTypes.yxmd

danrh

This should get you there:

Hope it helps!

solution.yxmd

hellyars

Thank you @john_miller9. This almost works. But, it still keeps the original A and B rows for Item 10001 (and all Items like 10001 when I try to scale it).

hellyars

This works @danrh great. But, I quickly ran into a problem. My real dataset also has a column for Quantity. Where Cost Type A will always have a value between 0 and N. Cost Type B and C will always be 0. Adding Quantity, trips up the approach. But, I am working amending the Quantity column back to the final result of your solution.

hellyars

It worked. Thank you.

I had to add a quantity field. I filtered out Quantity, solved for A-B, and appended Quantity to the result.

Screen Shot 2018-02-12 at 11.13.46 AM.png

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703